0 Replies Latest reply: Jan 16, 2013 5:27 AM by Jon Ander Olivera RSS

    Set Analysis to Exclude Certain Values

      Hello people,

       

      I am actually developing a waterfall-like chart in Qlikview and I have some problems to calculate some of the expressions.

       

       

      chart.jpg

       

      The bars coloured in blue represent the Impressions value for the selected period with 12 month rolling. This is correctly calculated.

       

      Erosion (in red) represents the sum of the Impressions from printers in the Q3-2011 period that do NOT print in Q3-2012 period.

       

      NEW IB (in green) represents the sum of the Impressions from printers in the Q3-2012 period that have started printing this period (and that did NOT print in Q3-2011).

       

      The expressions I have now are the following:

       

      EROSION

       

      0-

      sum({<DateComparison2={">$(=date(Date#(vQuarterEndDatePreviousYear2MinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY')) <=$(=date(Date#(vQuarterEndDatePreviousYearMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY'))"},

      [Serial Number]=e({<DateComparison2={">$(=date(Date#(vQuarterEndDatePreviousYearMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY')) <=$(=date(Date#(vQuarterEndDateMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY'))"}>} [Serial Number])>} Impressions)

      /1000000

       

      NEW IB

       

      sum

      ({<DateComparison2={">$(=date(Date#(vQuarterEndDatePreviousYearMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY')) <=$(=date(Date#(vQuarterEndDateMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY'))"},

      [Serial Number]=e({<DateComparison2={">$(=date(Date#(vQuarterEndDatePreviousYear2MinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY')) <=$(=date(Date#(vQuarterEndDatePreviousYearMinusOne,'DD/MM/YYYY'), 'MM/DD/YYYY'))"}>} [Serial Number])>} Impressions)

      /1000000

       

       

      I think that in both of the expressions the part of "DateComparison2" is correct, as it is only establishing the perior from which the impressions should be sumed.

       

      The problem on both Erosion and New IB expression resides on the exclusion part of the "Serial Number"s, which I think I am not doing correctly.

       

      How could I get the sum of the Impressions as I want?

       

      - Erosion (in red) represents the sum of the Impressions from printers in the Q3-2011 period that do NOT print in Q3-2012 period.

       

      - NEW IB (in green) represents the sum of the Impressions from printers in the Q3-2012 period that have started printing this period (and that did NOT print in Q3-2011).

       

      Thanks in advance,

       

      Jon