topic Re: set analysis count formula in QlikView

set analysis count formula

Thu, 14 Nov 2013 10:13:22 GMT

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?

Re: set analysis count formula

Thu, 14 Nov 2013 10:18:14 GMT

try this

Aggr(count(Customer),Customer,ProductCategory)

Re: set analysis count formula

kaushiknsolanki — Thu, 14 Nov 2013 10:19:21 GMT

Hi,

Have a look at the application attached.

Regards,

Kaushik Solanki

Re: set analysis count formula

Thu, 14 Nov 2013 10:34:48 GMT

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

kaushiknsolanki — Thu, 14 Nov 2013 10:41:16 GMT

Hi,

Try this expression.

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

Regards,

Kaushik Solanki