topic Re: Expression help timestamps in QlikView

Expression help timestamps

qliklizzy — Thu, 18 Jun 2015 15:32:20 GMT

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)

Re: Expression help timestamps

swuehl — Thu, 18 Jun 2015 15:39:58 GMT

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

)

Re: Expression help timestamps

qliklizzy — Thu, 18 Jun 2015 15:51:28 GMT

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)

Re: Expression help timestamps

swuehl — Thu, 18 Jun 2015 16:18:56 GMT

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.

Re: Expression help timestamps

qliklizzy — Fri, 19 Jun 2015 08:29:59 GMT

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

Re: Expression help timestamps

swuehl — Fri, 19 Jun 2015 10:58:20 GMT

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)

Re: Expression help timestamps

qliklizzy — Fri, 19 Jun 2015 11:38:39 GMT

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