Growth / Decrease

[pkelly]

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?

[#2]

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))

[johnw · Accepted solution]

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))