Source - Target with calendartimes

Hello Community,

anybody has an idea how to consider the calendar-times (which is related on the datamodel) in the following Source-Target function:

Preformatted text AVG(DAYS_BETWEEN(
SOURCE(
“Status_FY1718.csv”.“CHANGEDATE”,
REMAP_VALUES(“Status_FY1718.csv”.“STATUS”, [‘CLOSED’, NULL]),
FIRST_OCCURRENCE TO LAST_OCCURRENCE
),
TARGET(
“Status_FY1718.csv”.“CHANGEDATE”,
REMAP_VALUES(“Status_FY1718.csv”.“STATUS”, [‘CLOSED’, NULL]),
FIRST_OCCURRENCE TO LAST_OCCURRENCE
)
))
indent preformatted text by 4 spaces

FYI: the service times are
MONDAY 08:00 - 18:00 TUESDAY 08:00 - 18:00 WEDNESDAY 08:00 - 18:00 THURSDAY 08:00 - 18:00 FRIDAY 08:00 - 18:00

Best regards
Sarah

Hi Sarah,

I’m sorry but I’m struggling to understand what your trying to achieve. What do you mean by “consider”? Are you trying to find the days between the first and the last date where a change took place, ignoring all activities after the status was changed to closed? I don’t understand what service times have to do with anything. Could you please write back to us with more information so we can try to help you further?

Best wishes,

Calandra

Hi Calandra,

thank you for your question. I try to explain my scenario:

I have analyzed the Troughput-Times (or Solution Times) for a company’s activities (Service Desk tickets). I used SOURCE-TARGET to ignore the time of the status closed, because only this time matters for the TPT.So it counts all days (or hours, minutes) from every single day…24 hours, even on the weekend.
But I want to take into account the company’s working times (=service times), so that I can see their actual Troughput time of the activities.
I have deposit the times in the calendar of the data modell but the analyzes do not always take the calendar times into account.

Here is a good example:
I have a Servic Desk Ticket which was opened (status NEW) on Friday at 11:13 AM and was finished (status RESOLVED in this case) on Monday, 04:56 PM.

  • the analysis now calculates 3,24 days =77,65 hours as TPT

  • but there is a weekend between the activities, where the company didn’t work because their service times are only from Monday till Friday from 08:00 AM till 6:00 PM.

  • therefore the actual TPT has to to be round about 15 hours = 0,63 days instead of 3,24 days.

image

Now I want to include the service-times in my SOURCE-TARGET function to get the right TPT (0,63 days) in the following OLAP table. How is this possible?

I hope my problem becomes apparent - please let me know, if not

Thank you in advance

Sarah

Hi Sarah,

You can incorporate the Service times using REMAP_TIMESTAMPS. If instead of using “Status_FY1718.csv”.“CHANGEDATE” you use the following code instead, the times outside the specified time slots aren’t included in the calculation of the time difference:

REMAP_TIMESTAMPS ( “Status_FY1718.csv”.“CHANGEDATE”, DAYS, MONDAY 08:00-18:00 TUESDAY 08:00-18:00 WEDNESDAY 08:00-18:00 THURSDAY 08:00-18:00 FRIDAY 08:00-18:00)
It’s important to note that REMAP_TIMESTAMPS returns an integer not a date. It’s the number of days (not including weekends as your service times as specified in the function are Monday to Friday) since the reference date its starts counting from (1.1.1970 I believe). This just means your code needs to be modified so that instead of using days_between you subtract source from target.

The code would look like this:

AVG(
TARGET(
REMAP_TIMESTAMPS ( “Status_FY1718.csv”.“CHANGEDATE”, DAYS, MONDAY 08:00-18:00 TUESDAY 08:00-18:00 WEDNESDAY 08:00-18:00 THURSDAY 08:00-18:00 FRIDAY 08:00-18:00),
REMAP_VALUES(“Status_FY1718.csv”.“STATUS”, [‘CLOSED’, NULL]),
FIRST_OCCURRENCE TO LAST_OCCURRENCE) -
SOURCE(
REMAP_TIMESTAMPS ( “Status_FY1718.csv”.“CHANGEDATE”, DAYS, MONDAY 08:00-18:00 TUESDAY 08:00-18:00 WEDNESDAY 08:00-18:00 THURSDAY 08:00-18:00 FRIDAY 08:00-18:00),
REMAP_VALUES(“Status_FY1718.csv”.“STATUS”, [‘CLOSED’, NULL]),
FIRST_OCCURRENCE TO LAST_OCCURRENCE)
)

I hope this solve your problem; if you have any further issues let us know.

Best wishes,
Calandra

1 Like

Hi Calandra,

I just had to add the braces “[”]" after FIRST_OCCURENCE and LAST_OCCURENCE to your code and then it worked! Thank you for your help!

The only thing -which I’m now struggeling with- is that even if I add *1.0 to the code, there is no chance to get the full figures. It just returns the rounded figures “1,00 days” instead of 0,63 days.

Do you have an idea for that, too?

Thank you in advance

Sarah

Hi Sarah,

Strange, when I was testing the solution, I was able to get decimal numbers

What formatting are you using? Does it help if you change it to "Decimal Number (#.##) ?

Alternatively, you could change “DAYS” to “HOURS” in the REMAP_TIMESTAMPS function and then in your KPI divide by 24.

Let me know if this doesn’t solve the problem.

Best wishes,

Calandra

Hi Calandra,

it confuses me too. No matter what I try, it just returns rounded numbers.
I also tried your proposal to create the same KPI in hours and divide it by 24. Before division it returns back 15.00 h, after that it returns 0.00 h

Look at this:

I have the feeling this is due to the return value INTEGER, which is automatically deposited for REMAP_TIMESTAMPS. Is there a way to change that into decimal or float?

Best wishes
Sarah

Hello Sarah,

yes, this seems to be an issue with Data types. Is it possible that you are using CPM4.4 or an earlier version? On the IBC and on CPM4.5, AVG and the Division return a float by default, independent of the input data type.

On CPM4.4, you need to cast integer values manually. You can simply do this by multiplying it with 1.0.
So you would write something like

AVG ( (TARGET(...)-SOURCE(...) ) * 1.0 ) / 24

The AVG should then return a Float value, and therefore the Division should return a Float as well.

@c.eckert Did you try it on IBC or CPM4.5? Then this would explain why you get Float numbers from the beginning :wink:

Best
David

1 Like

Oh yes David, thats it. (FYI I’m using CPM4.4)
I was wondering why there were no changes, when I tried to multiply the DAYS with 1.0 earlier.
But it works perfectly with the proposal of Calandra (to take HOURS instead of DAYS and divide it with 24) and then multiply this result with 1.0.
Thank you both for your help!

Best Greetings
Sarah

Hey Sarah,

REMAP_TIMESTAMPS always returns an INT. So when you specify DAYS, it returns the number of days as an INT. That means that this is already a rounded number then. The precision cannot be better than days. So multiplying with 1.0 cannot fix the precision here.

Therefore, using HOURS also does not return the most exact value since its precision is “only” hours, although this might be enough for your use case. If you want your result to be even more precise, you need to use MINUTES or even SECONDS and then divide by the number of minutes or seconds per day accordingly.

Best,
David

1 Like