Hi Sarah,

Im sorry but Im 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 dont 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 companys 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 day24 hours, even on the weekend.

But I want to take into account the companys 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 didnt 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.

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?

image.png89796 8.29 KB

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 arent 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)

Its important to note that REMAP_TIMESTAMPS returns an integer not a date. Its 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

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 Im 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 (#.##) ?

Change number format.PNG73858 6.9 KB

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 doesnt 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:

image.png19331383 239 KB

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

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

Best

David

Oh yes David, thats it. (FYI Im 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