2 Replies Latest reply: Jun 2, 2014 1:07 PM by John McCarthy RSS

    Set Analysis problem

    John McCarthy

      This is my first post in the QLik Community so be nice

       

      I have a problem where i need to show separate lines on a graph for seperate years.  The following works OK.

       

      sum

      ({$<Del.WeekYear={$(=max(Del.WeekYear))}> } DeliveryQty)

       

      and this also works

       

      sum

       

      ({$<Del.WeekYear={$(=max(Del.WeekYear-1))}> } DeliveryQty)

       

      However i now need to graph a number which I need to calculate as follows:

      The number is calculated using the following formula which works. 

      sum

       

      ({<DisplayName={'Main Electricity'} >} Data )/((Sum({$<DisplayName={'Weigh Belt'} > } Data)
      )/1000
      * (1-
      Avg({$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

      I now need to graph this in the same fashion as the first example but don't appear to be able to get the syntaz right to apply the set

      ({$<Del.WeekYear={$(=max(Del.WeekYear-1))}> }

       

      i have tried:

       

      sum

       

      ({$<Del.WeekYear={$(=max(Del.WeekYear))}> }{<DisplayName={'Main Electricity'} >} Data )/((Sum({$<DisplayName={'Weigh Belt'} > } Data)
      )/1000
      * (1-
      Avg({$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

       

      and

       

      sum

       

      ({$<Del.WeekYear={$(=max(Del.WeekYear-1))}> } {<DisplayName={'Main Electricity'} >} Data )/((Sum({$<DisplayName={'Weigh Belt'} > } Data)
      )/1000
      * (1-
      Avg({$<DisplayName={'Moisture'}, Data={">1"} > } Data)/100))

       

      Both of these formulas result in the same line being graphed even though :

      {$<Del.WeekYear={$(=max(Del.WeekYear))}> } evaluates to 2014

      {$<Del.WeekYear={$(=max(Del.WeekYear-1))}> } evaluates to 2013

       

      Any suggestions please?

      thanks in advance

      John