Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am currently trying to build an expression for a pivot table.
In this table, I want to display the number of customers, which have a sales volume above a certain level and for a specific product group only.
The background data would look like following
Customer | Product Category | Sale |
---|---|---|
Customer 1 | A | 300 |
Customer 1 | A | 400 |
Customer 1 | B | 500 |
Customer 2 | A | 100 |
Customer 2 | B | 600 |
Customer 3 | A | 1000 |
The counter should now display e.g. the number of customers, which have an (aggregated) sales volume above 500 for product category A only.
So in this context, the result of that counter would be 2 (customer 1 having a total sales volume of 700 for category A and Customer 3 with an amount of 1000 for A).
The difficulty I see is, how to create a count formula with a nested sum or aggr formula.
Does anyone know, how to set up a formula for that purpose?
Hi,
Try this expression.
count({<Customer = {"=sum({<ProductCategory = {'*A*'}>}Sale)>$(vTest)"}>}DISTINCT Customer)
Regards,
Kaushik Solanki
hi
try this
Aggr(count(Customer),Customer,ProductCategory)
Hi,
Have a look at the application attached.
Regards,
Kaushik Solanki
Hi Kaushik
thanks for that quick proposal.
This already goes into the right direction.
I tried to modify the formula used in your application, to explicitely set the sales amount level to 500 (instead of using a dynamic input box).
Further more, I would like to count the customers for category A only (independent from a selection).
I modified the formula towards this syntax:
count({<Customer = {'=sum(Sale)>=500'}, [Product Category]={A}>}DISTINCT Customer)
The sales amount modification works however, restricting the counter to product category A only, does not work yet.
Any idea, what needs to be adjusted to make the counter independent from the product argument selection and count only the customers for category A?
Hi,
Try this expression.
count({<Customer = {"=sum({<ProductCategory = {'*A*'}>}Sale)>$(vTest)"}>}DISTINCT Customer)
Regards,
Kaushik Solanki