Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

Community Browser