Set analysis(?) challenge

Fri, 23 Sep 2011 09:28:11 GMT

Hi,

Challenge:

- Show, in a pivot table, for each date in, the accumulated planning for the coming 5 days

- Show maximum 5 dates starting the last date in the selection

For example, if sep 22nd is selected, for each date, show the accumulated planning for the coming 5 days = (DateX) + (DateX+1) + (DateX+2)+ (DateX+3)+ (DateX+4)

Date (calculated) dimmension

=If(RefDate <= '$(vceMaxDate)' and RefDate >= '$(vceMinDate)',RefDate)

Whereas :

RefDate field - Date from date island (not connected to the rest of the data model)

vceMinDate variable - should be the last date on selection = Max(Date)

vceMaxDate variable = vceMinDate + 4

Challenge1: calculated dimmension does not allow aggregation

Planning expression

Sum(If(Date >= aggr(NODISTINCT max(RefDate),MachineId,RefDate) and

Date <= Date(aggr(NODISTINCT max(RefDate),MachineId,RefDate)+4),planning)

This will not work when a single date is selected. Qlikview will, then, only show the planning for that single day.

This calls for Set Analysis but as far as i know, it calculates one time per object

Challenge2: use set analysis to accumulate planning but disregarding date selection

I've attached an example.

appericiate your help,

Dror

Re: Set analysis(?) challenge

swuehl — Fri, 23 Sep 2011 09:53:36 GMT

Hi drorsvr,

maybe like attached?

I just added a set identifier {1} to your table expression. And set your vceminDate Variable to =max(Date) (I think your calendar input box is somehow contra productive here).

So if I select a Date from the list box a the right, I should get the correct planning for following 5 days.

regards,

Stefan

Set analysis(?) challenge

Fri, 23 Sep 2011 11:01:33 GMT

Perfect! Thanks!!!

topic Set analysis(?) challenge in QlikView

Set analysis(?) challenge

Date (calculated) dimmension

Planning expression

Re: Set analysis(?) challenge

Set analysis(?) challenge