4 Replies Latest reply: Nov 14, 2013 5:41 AM by Kaushik Solanki

# set analysis count formula

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

CustomerProduct CategorySale
Customer 1A300
Customer 1A400
Customer 1B500
Customer 2A100
Customer 2B600
Customer 3A1000

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?

• ###### Re: set analysis count formula

hi

try this

Aggr(count(Customer),Customer,ProductCategory)

• ###### Re: set analysis count formula

Hi,

Have a look at the application attached.

Regards,

Kaushik Solanki

• ###### Re: set analysis count formula

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?

• ###### Re: set analysis count formula

Hi,

Try this expression.

count({<Customer = {"=sum({<ProductCategory = {'*A*'}>}Sale)>\$(vTest)"}>}DISTINCT Customer)

Regards,

Kaushik Solanki