Set analysis(?) challenge

[#1]

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

JHi

Challenge:

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

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

[swuehl · Accepted solution]

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

[#3]

Perfect! Thanks!!!