Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a field MTTR Current Month in format d:hh:mm. Like below:
Expression is like below:
interval((Sum({$<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'$(=$(MaxMonth))'}>}Req_Duration)*1440/Count({$<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'$(=$(MaxMonth))'}>}distinct Req_number))/1440,'d:hh:mm')
I want to make this as 4 Days 21 Hours 32 Mins instead of 4:21:32
Is there any way to implement this?
Please help.
Thanks,
Sarif
try
subfield(Field,':',1) & ' Days' & subfield(Field,':',2) & ' Hours' & subfield(Field,':',3) & ' Mins'
we cant use this as my calculation is complex. If we could handle this using interval function, this would be helpful.
Thanks,
Sarif
I don't know of an easy way to do this without repeating the expression count. So this works
=Floor(4.2) & ' days ' & Time(4.2, 'h') & ' hours ' & Time(4.2, 'm') & ' minutes'
but when you replace 4.2 with the actual expression, you will be calculating the expression 3 times.
As this is a simple KPI, you could define a globally evaluated variable (ie with a leading zero):
Set vAvgDur = =Sum({#<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'#(=#(MaxMonth))'}>}Req_Duration) / Count({#<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'#(=#(MaxMonth))'}>}distinct Req_number)
Let vAvgDur = Replace(vAvgDur, '#', '$');
The extra = is not a typo. You need to do this in two steps to prevent expansion of the $ expansions when the script is running.
Now you can write the expression:
=Floor($(vAvgDur)) & ' days ' & Time($(vAvgDur), 'h') & ' hours ' & Time($(vAvgDur), 'm') & ' minutes'
If you leave off the leading = sign, the expression will still work, but it will be evaluating the expression 3 times. The leading = ensures that the expression is expanded as a value.
I think it is not possible to directly define the format that you want in Interval function.
But you could do that :
=Replace(Replace(Replace( interval((Sum({$<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'$(=$(MaxMonth))'}>}Req_Duration)*1440/Count({$<Req_flag= {'Closed'},Req_state={3},[Req_Month_Year]={'$(=$(MaxMonth))'}>}distinct Req_number))/1440, 'd x1 hh x2 mm x3'), 'x3', 'Minutes'), 'x2', 'Hours'), 'x1', 'Days')