Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
maybe you techs could help me by coding a KPI.
I have two dates. Createddate und Closeddate. Now i want to calculate the runtime.
Closeddate-Createdate ---> But only in office hours. The runtime should only be calculated between Mo-Fr / 06:00-23:00.
I do not have any idea, how to do this.
BR Christian
Maybe have a look at
Calculate hours between two Date/Time strings
It shows some different approaches ( script / chart expression) to calculate the interval excluding e.g. non working hours.
Hey, in that way i got the hours between. But to get the hours without workdays and only from 06:00 to 23:00 ??
Nothing new under the sun))
Try Stefan's expression, works fine
Re: Calculate hours between two Date/Time strings
Interval(
Sum(rangesum(
NetWorkDays(Createdate+1,Closeddate-1) * MakeTime(17)
,if(NetWorkDays(Closeddate,Closeddate),Rangemin(rangemax(frac(Closeddate),maketime(6)),maketime(23))-Rangemax(rangemin(frac(Closeddate),maketime(6)),maketime(6)),0)
,if(NetWorkDays(Createdate,Createdate),Rangemin(rangemax(frac(Createdate),maketime(23)),maketime(23))-Rangemax(rangemin(frac(Createdate),maketime(23)),maketime(6)),0)
,if(NetWorkDays(Createdate,Createdate) and floor(Createdate)=floor(Closeddate),-MakeTime(17))
))
,'hh:mm')
Hey,
i got not relaistic data. Do you know why ? Because of AM and PM isntìt ? So i have to load the data in /24 format ?
Thanks for your answer.
Can't reproduce this here
Please post some sample records as INLINE table.
That's what I see in my app with this expression.
Not sure, but check these variables and try these values
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss';
Thanks for all the help. Skript is now working.
Hey, maybe you guys could help me with the last calculation.
I used the script, which i working fine, to get the hours. Now i have pausedminutes which can be 1710 Minuten. I convert the minutes into the same unit. When i try to subtract the pausedminutes from the hours, i get strange numbers.
To get hours from minutes i use the following code:
Interval(PausedMinutes/24/60,'hh.mm')
The script for the hours:
Interval(
Sum(rangesum(
NetWorkDays(CreationDate+1,ClosedDateTick-1) * MakeTime(17)
,if(NetWorkDays(ClosedDateTick,ClosedDateTick),Rangemin(rangemax(frac(ClosedDateTick),maketime(6)),maketime(23))-Rangemax(rangemin(frac(ClosedDateTick),maketime(6)),maketime(6)),0)
,if(NetWorkDays(CreationDate,CreationDate),Rangemin(rangemax(frac(CreationDate),maketime(23)),maketime(23))-Rangemax(rangemin(frac(CreationDate),maketime(23)),maketime(6)),0)
,if(NetWorkDays(CreationDate,CreationDate) and floor(CreationDate)=floor(ClosedDateTick),-MakeTime(17))
))
,'hh.mm')
Now i try to subtract:
(Interval(
Sum(rangesum(
NetWorkDays(CreationDate+1,ClosedDateTick-1) * MakeTime(17)
,if(NetWorkDays(ClosedDateTick,ClosedDateTick),Rangemin(rangemax(frac(ClosedDateTick),maketime(6)),maketime(23))-Rangemax(rangemin(frac(ClosedDateTick),maketime(6)),maketime(6)),0)
,if(NetWorkDays(CreationDate,CreationDate),Rangemin(rangemax(frac(CreationDate),maketime(23)),maketime(23))-Rangemax(rangemin(frac(CreationDate),maketime(23)),maketime(6)),0)
,if(NetWorkDays(CreationDate,CreationDate) and floor(CreationDate)=floor(ClosedDateTick),-MakeTime(17))
))
,'hh.mm'))-(Interval(PausedMinutes/24/60,'hh.mm'))
The result should be: 00.47
BR Christian