topic Growth / Decrease in QlikView

Growth / Decrease

pkelly — Tue, 17 Nov 2009 23:37:23 GMT

I have a flat sales table which, by customer, I have the sales for the past 5 financial years.

The user can pick a base finanical year and a comparison financial year ** and, on a detailed pivot, I show if the customers sales have either increased or decreased.

Base / comparison year is achieved by having buttons for each year which update variables varBase and varComparison.

Pivot table compares financial year with varBase and sums sales for same.

Comparison sales appear in a different column using same approach as above except with varComparison.

This works fine...

I have been asked to produce a second pivot table where, comparing varComparison against varBase, the user wants to see...

1) How many customers sales have grown.

2) The total sales for these "Growth" customers

3) How many customers sales have decreased.

4) The total sales for these "Decrease" customers.

Can anyone please give me some assistance as to how I achieve this?

Growth / Decrease

Wed, 18 Nov 2009 00:33:20 GMT

Paul,

I think below is something like what you are looking for:

1) How many customers sales have grown.

=count(distinct if(sales>0,customer_no))
OR
=count(distinct if(varComparison>0,customer_no))

2) The total sales for these "Growth" customers

=sum(if(sales>0,sales))
OR
=sum(if(varComparison>0,sales))

3) How many customers sales have decreased.

=count(distinct if(sales<=0,customer_no))
OR
=count(distinct if(varComparison<=0,customer_no))

4) The total sales for these "Decrease" customers.

=sum(if(sales<=0,sales))
OR
=sum(if(varComparison<=0,sales))

Growth / Decrease

johnw — Wed, 18 Nov 2009 00:46:03 GMT

So your growth column is something like sum({<Year={$(varBase)}>}Sales)-sum({<Year={$(varComparison)}>}Sales)? For that, here are my untested guesses for your new expressions:

1) How many customers sales have grown.

sum(aggr(if(sum({<Year={$(varBase)}>}Sales)>sum({<Year={$(varComparison)}>}Sales),1),Customer))

2) The total sales for these "Growth" customers

sum(aggr(if(sum({<Year={$(varBase)}>}Sales)>sum({<Year={$(varComparison)}>}Sales),sum({<Year={$(varBase)}>}Sales)),Customer))

3) How many customers sales have decreased.

sum(aggr(if(sum({<Year={$(varBase)}>}Sales)<sum({<Year={$(varComparison)}>}Sales),1),Customer))

4) The total sales for these "Decrease" customers.

sum(aggr(if(sum({<Year={$(varBase)}>}Sales)<sum({<Year={$(varComparison)}>}Sales),sum({<Year={$(varBase)}>}Sales)),Customer))