2 Replies Latest reply: Sep 23, 2011 7:01 AM by Dror Svartzman RSS

    Set analysis(?) challenge

    Dror Svartzman

      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