Expression help timestamps

[qliklizzy]

Hi

I have this Expression set up to give me the % of lists that finish more than 30 minutes late.

which works fine BUT I need to get it to show it in the opposite way and either side of the end time.

basically I need to to show % of lists that finish 30 minutes within the session end time, so either 30 minutes before and up to schedule end or 30 minutes after.

hope i havent confused anyone i'm doing it to myself very nicely though

(frac(MaxOperationEnd) < frac(Time(MaxSessionEnd) + Num(Timestamp(0.04166667) /60 * 30)),1,0))/ count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

[swuehl]

Are you missing something like sum(if( ... at the front of your expression you've posted?

anyway, I think you need something like

(

frac(MaxOperationEnd) <  (frac(Time(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) >  (frac(Time(MaxSessionEnd) - interval#('0:30','h:mm'))

)

[qliklizzy]

Hi

thanks for that I have tried it and it sounds like thats the kind of thing i need;

but adding it in it hasnt worked it could be the brackets ?:

(sorry i did leave the first part off)

sum({<OperationOrder={1},MaxOperationEnd ={"<>''"}>}  {<CancelledOnDay={0}>}

if(frac(MaxOperationEnd) <  (frac(Time(MaxSessionEnd) + interval#('0:30','h:mm')) and frac(MaxOperationEnd) >  (frac(Time(MaxSessionEnd) - interval#('0:30','h:mm'))/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

[swuehl]

Yes, seems that you missed some closing brackets:

sum({<OperationOrder={1},MaxOperationEnd ={"<>''"}>}  {<CancelledOnDay={0}>}

if(

frac(MaxOperationEnd) <  (frac(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) > (frac(MaxSessionEnd) - interval#('0:30','h:mm'))

)

)

/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

You don't need to format your MaxSessionEnd using Time() function.

Your set expression in the outer sum() looks kind of strange, but I guess that's another topic.

[qliklizzy]

thanks again ...its still not working i think i have too many brackets now. its underlining the 3rd bracket after the second interval bit  .. ?

thanks sorry to be a pain

wish I understood this more

[swuehl · Accepted solution]

Yes, I think by the use of copy & paste, we missed the acutal THEN / ELSE branches of your if() statement.

To get the functions and bracketing correct, it's often good to set the cursor right before a bracket, then the pairing closing / opening bracket will be highlighted. Also the tooltip indicating the syntax of functions is helpful in debugging an expression.

Try this

sum({<OperationOrder={1},MaxOperationEnd ={"<>''"}>}  {<CancelledOnDay={0}>}

     if(

          frac(MaxOperationEnd) <  (frac(MaxSessionEnd) + interval#('0:30','h:mm'))

          and

          frac(MaxOperationEnd) > (frac(MaxSessionEnd) - interval#('0:30','h:mm'))

          ,1,0

     )

)

/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

[qliklizzy]

thank you so much for spending time on this for me  - perfect!