New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor 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?

Thanks,

Sarif

5 Replies Partner

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'

Contributor II

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein 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.

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

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