Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

Format Change of d:hh:mm to d Day hh Hour mm Mins

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

5 Replies
shraddha_g
Partner - Master III
Partner - Master III

try

subfield(Field,':',1) & ' Days' & subfield(Field,':',2) & ' Hours' & subfield(Field,':',3) & ' Mins'

mhmmd_srf
Creator II
Creator II
Author

we cant use this as my calculation is complex. If we could handle this using interval function, this would be helpful.

Thanks,
Sarif

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sfatoux72
Partner - Specialist
Partner - Specialist

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