Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mhmmd_srf
Contributor

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
Honored Contributor III

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

try

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

mhmmd_srf
Contributor

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

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

Thanks,
Sarif

MVP
MVP

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

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.

MVP
MVP

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

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.

sfatoux72
Valued Contributor

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

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

Community Browser