topic Re: Present limited sales amount in QlikView

Present limited sales amount

Fri, 05 Apr 2013 10:25:42 GMT

See below straight table. I want to present only the values of customers where the total sales amount is greater or equal than 10.000 and smaller or equal than 20.000. So I only want to see customers X and Z. How do I realise this?

Customer name	Min. price	Max. price	Sales amount
Customer S	10,00	20,00	500
Customer X	15,00	25,00	10.000
Customer Y	25,00	50,00	50.000
Customer Z	20,00	30,00	15.000

Re: Present limited sales amount

prasad_dumbre — Fri, 05 Apr 2013 10:39:28 GMT

sum(if(sum(sales_amt)>=10000 and sum(sales_amt)<=20000,sales_amt))

Re: Present limited sales amount

Gysbert_Wassenaar — Fri, 05 Apr 2013 11:19:24 GMT

You can use this expression: sum({<[Sales amount]={'>=10000<=20000'}>}[Sales amount])

See attached example.

Re: Present limited sales amount

Fri, 05 Apr 2013 12:14:18 GMT

It is working with below expression, only now you see all instead of only the limited records.
Do you have also a solution for this?

if (sum(sales_amt)>=10000 and sum(sales_amt)<=20000,sum(sales_amt))

Re: Present limited sales amount

Fri, 05 Apr 2013 12:23:16 GMT

Your example is exactly what I need! Unfortunately the expression is not working in my table. It still shows records under 10.000 and above 20.000.

The sales amount per customer is based on individual sales records per customer.

Does the first part of your expression ({<[Sales amount]={'>=10000<=20000'}>} needs a sum of sales amount?

If yes, how do I realize this?

Re: Present limited sales amount

Gysbert_Wassenaar — Fri, 05 Apr 2013 14:01:41 GMT

Ah, my mistake. Try this:

sum({<[Customer name]={"=sum([Sales amount])>=10000 and sum([Sales amount])<=20000"}>} [Sales amount])

Replace [Customer name ] and [Sales amount] with your exact field names.

Re: Present limited sales amount

Fri, 05 Apr 2013 14:24:47 GMT

This is working great, but now I see all instead of only the limited records.

In your example you see only the limited records of the remain customers. I want this too. Do you know a solution for this?

Re: Present limited sales amount

Fri, 05 Apr 2013 15:02:31 GMT

Are min and max price- expressions in you example? If so, you should add above set analysis:

({<[Customer name]={"=sum([Sales amount])>=10000 and sum([Sales amount])<=20000"}>})

in all of them.