topic How to select distinct previous? in QlikView

How to select distinct previous?

Tue, 12 Apr 2011 17:51:02 GMT

Hello,
data:
LOAD * INLINE [
Issue, Date, hrs,
A-100, 1/1/2011, 6
A-101, 1/1/2011, 10
A-102, 1/1/2011, 6
A-101, 1/7/2011, 12
A-102, 1/7/2011, 4
A-103, 1/7/2011, 15
A-103, 1/14/2011, 18
];

Trend:
LOAD * INLINE [
TDate, Week
1/1/2011, 1
1/7/2011, 2
];

I need to do the following
calculate the diff in hrs for each date with respect to the previous date
so that
when user selects Date 1/14/2011 from a multiselector, the calculated value for diff should be (Hrs for 1/14/2011) - (Hrs for 1/7/2011) and when 1/7/2011 is selected from a multibox, the calculated value for diff should be (Hrs for 1/7/2011) -(Hrs for 1/1/2011)

How can I achieve this?

How to select distinct previous?

vgutkovsky — Tue, 12 Apr 2011 18:51:43 GMT

All things are possible through set analysis 🙂 First, I would suggest adding an index to your script, similar to what you've done with "Week." Just make sure you actually link it to the data--rename TDate to Date. Then create a variable called vMaxWeek which you would define as follows: =max(Week)-1

Then this code should work for your expression: sum(hrs) - sum({<Date=,Week={'$(vMaxWeek)'}>}hrs)

Note that this won't work within a chart since set analysis is calculated once for an entire chart and ignores dimensions. Meaning, you can't create a chart with Date as a dimension and use the above expression. If you need to do that, you can always use the above() function.

Regards,

How to select distinct previous?

Wed, 13 Apr 2011 19:01:56 GMT

Thanks a lot,

Its works like magic!

How to select distinct previous?

vgutkovsky — Wed, 13 Apr 2011 19:50:31 GMT

Then please verify the answer 🙂