Set Analysis to Exclude Certain Values

Wed, 16 Jan 2013 10:27:26 GMT

Hello people,

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

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

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

topic Set Analysis to Exclude Certain Values in QlikView

Set Analysis to Exclude Certain Values