Intervall function with set expression AND additional condition

[Applicable88]

Hello,

I'm using a intervall function like this:

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

As you can see I want to return the average duration  (FinishedTime minus StartedTime), but only for the Action when its on 'charging' mode. 

And additional to that it has to be the data where when its finished was on the same day as where I started it. 

So I guess the two field names FinishedTime and StartedTime cannot be just put in an equation within the  setexpression? 

Thank you in advance.

Best. 

[Kushal_Chawda]

@Applicable88  try below

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

[Applicable88]

Hi @Kushal_Chawda ,

thank you very much. 

Why the statement actually needs to be in double quotes? {"=FinishedTime1=StartedTime1"}

Since its already recognized as datefields, I wonder because shouldn't it search line by line automatically?

Best. 

[Kushal_Chawda · Accepted solution]

@Applicable88  This is how set analysis works. If you want to check it line by line then you may need to go with if condition like below

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

[Applicable88]

@Kushal_Chawda , good to know the alternative! I got the same results. I just needed floor() function for the two Time fields, than it works out as well. 

As I see it the way you wrote it as set expression and also as if-statement is searching line by line. Only if doing something with DSE would be different. 

Thank you so much.