Interval function with a set expression instead of 'if'

[Applicable88]

Hello,

I'm using following statement for an KPI Window:

AVG(if(Action= 'charging', interval(FinishedTime-StartedTime,'hh:mm:ss')))

I tried to put the same as an set expression, because set expression gives me far more option like "current selection", "exclude" etc. 

Wherever I put the set expression like this:

{$<Action={'charging'}>}

It doesn't seem tow work. I guess its the syntax, which didn't work out. 

Have someone an idea?

Thank you in advance.

Best.

[tm_burgers]

This seems to work for me; just ensure that your Measure number formatting is set to Duration otherwise Auto will display the result in Days. 

avg({$<Action={'charging'}>} interval(FinishedTime-StartedTime,'hh:mm:ss'))

your other option is to add the interval to the data set itself. 

[Applicable88]

Hello @tm_burgers ,

thanks for your quick reply. Formatting is already set via Load Script.

It works out so far.

Do you also have an idea when I only want the average for these duration where the Date  of start and finished time is on the same day (excluding overnight)

The following is not working out: 

avg({$<Action={'charging'},Date(FinishedTime=Date(StartedTime)}>}interval(FinishedTime-StartedTime,'hh:mm:ss'))

Thank you very much. 

[tm_burgers · Accepted solution]

I would create a flag in the load script to use in your analysis:

ie. 

if(num(floor(Date(FinishedTime)))=num(floor(Date(StartedTime))),1,0) as CompletedSameDay

 then your set analysis would be :

avg({$<Action={'charging'},CompletedSameDay={'1'}>}interval(FinishedTime-StartedTime,'hh:mm:ss'))

[Applicable88]

Hi @tm_burgers ,

thats a very good tip.

I put it into the load script. 

But even if the date is the same it still gives me '0' :

Can you see the left one is the flag from load script and the right one is when I put it directly into the table. 

So I know the function actually works, but as a loaded "CompleteSameDay" its always zero. 

[tm_burgers · Accepted solution]

I think it depends how your Date field is loaded; you may have to wrap FinishedTime or StartedTime in either a Date#() or Timestamp#() to define how they are entered; since Qlik may be performing the Date conversion after the table is loaded.

[Applicable88]

@tm_burgers , thank you. It definitely has something to do with either interpretation or formatting. 

I will try to change it in the loadscript. And yes....of course the formula works in the table , because the interpretation and the formatting is already done through the loading. I didn't consider that a new calculated column will need me to do this again. 

Thanks for your patience.

Best.