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

    Set analysis(?) challenge

    Dror Svartzman




      - 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,