topic Re: How to create expression to do a calculation using previous record value in QlikView

How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 15:41:06 GMT

I need to create an expression for a straight table for a column to subtract previous surgery stop time from the next surgery start time, if on same day. I've tried using variables but discovered you can't set those values in an expression. I don't know how to check if 1) the same date and 2) use previous records' stop time. Is that possible in an expression?

Re: How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 16:13:44 GMT

Carmalita, could you post en example set of data ? or an example app ? it would be helpful to understand visually what you are trying to achieve

Re: How to create expression to do a calculation using previous record value

ecolomer — Tue, 16 Sep 2014 16:14:31 GMT

Use function ABOVE

Re: How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 16:33:26 GMT

I have a chart type of Straight Table with columns:

surgery_date surgery_start_time surgery_stop_time NEW COLUMN FOR TURN-AROUND TIME

09/16/2014 08:00 09:00 00 min (first record of day not applicable)

09/16/2014 09:15 10:15 15 min (start time minus prev record stop time)

09/17/2014 08:00 08:30 00 min

09/17/2014 10:00 11:00 90 min (10:00-8:30)

I need a column to show turn-around time from end of one surgery to start of next surgery (for that day).

Thank you!

Re: How to create expression to do a calculation using previous record value

maxgro — Tue, 16 Sep 2014 16:41:14 GMT

search in online help

Chart Inter Record Functions

as Enrique said

Re: How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 16:42:22 GMT

I tried your suggestion only used the BELOW function (for last/prior?) and my formula returns all nil values ?

SURG_PIR_TIME_START - BELOW(SURG_PIR_TIME_STOP)*1440

My table is sorted in SURG_PIR_TIME_START (date/timestamp) order. So not sure what I'm doing wrong.

Thank you.

Re: How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 16:50:10 GMT

I tried ABOVE but also nil results:

SURG_PIR_TIME_START-ABOVE(SURG_PIR_TIME_STOP))*1440

Re: How to create expression to do a calculation using previous record value

maxgro — Tue, 16 Sep 2014 17:53:30 GMT

see attachment

Re: How to create expression to do a calculation using previous record value

Tue, 16 Sep 2014 18:08:46 GMT

That should work for me. I"m having some syntax issues but will work it out. Thanks so much.

Re: Re: How to create expression to do a calculation using previous record value

Wed, 17 Sep 2014 13:37:12 GMT

Massimo

If I have 2 columns in my straight table, SURG_TIME_STOP and Above(SURG_TIME_STOP) the Above function works fine.But if I add another column it doesn't. Why would that be? I'm attaching screenshots of my properties.

Thanks so much for your input.

Carmalita