topic Re: CountIf CumulativeSum is < value. in QlikView

CountIf CumulativeSum is < value.

Fri, 31 May 2013 15:50:26 GMT

I have three fields, Item, Customer and Percent Sales.

For each Item, I need to know how many Customers it takes to accumulate at least 80% sales.

For instance:

Item	Customer	PercentSales	Cumulative Sales
1	1	10%	10%
1	2	25%	35%
1	3	10%	45%
1	4	20%	65%
1	5	20%	85%
1	6	5%	90%
1	7	5%	95%
1	8	5%	100%
2	1	5%	5%
2	2	5%	10%
2	3	5%	15%
2	4	5%	20%
2	5	20%	40%
2	6	20%	60%
2	7	25%	85%
2	8	10%	95%
2	9	5%	100%

Would give me the results:

Item	Count:CumulativeSales>=80
1	5
2	7

In Excel I could do a countif(CumulativeSales<=.8)+1

How could I do this in Qlikview?

Please help with the cummulative sum and the count statement

Thank you!

Re: CountIf CumulativeSum is < value.

RedSky001 — Fri, 31 May 2013 16:18:40 GMT

Create a chart with item as the dimension and the following expresison.

count(DISTINCT if([Cumulative Sales] <= 0.8,Customer))

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 16:22:39 GMT

I don't have a field for cumulative sum. I only have Item, Customer and PercentSales. Can you help me figure out how to get CumulativePercentSales first, then I can try the above.

Thanks!

Re: CountIf CumulativeSum is < value.

RedSky001 — Fri, 31 May 2013 16:24:10 GMT

create a chart with customer as the dimesion and the following expresison with RT as the label.

rangesum(sum(PercentSales), above(RT))

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 16:39:34 GMT

Unfortunately, that's not working.

If I do it only over customer, it's combining from multiple items.
If I do it over item and customer, the sums aren't making sence (should never go over 1)

I attached an example to my original post Please fix

Thanks, Melanie

Re: CountIf CumulativeSum is < value.

RedSky001 — Fri, 31 May 2013 17:09:38 GMT

See the example.

I am rushing this as I'm about to go home, but let me know how it looks.

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 17:15:46 GMT

I need to add +1 to your count to make it work out right. (number <80 + 1 to count customer that makes it over 80)

I think this will work. Thank you so much, I will play around.

Cheers!

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 17:38:50 GMT

I left off one more parameter, which is still throwing things off.

I need it to do the cumulative percent based on an ordered list, PercentSales Desc

Any idea how to modify?

So close!

Re: CountIf CumulativeSum is < value.

RedSky001 — Fri, 31 May 2013 18:58:06 GMT

Are you saying the data sould be sorted like this:

Item	Customer	PercentSales
1	6	50.00%
1	7	50.00%
1	8	50.00%
2	1	50.00%
2	2	50.00%
2	3	50.00%
2	4	50.00%
2	9	50.00%
1	2	25.00%
2	7	25.00%
1	4	20.00%
1	5	20.00%
2	5	20.00%
2	6	20.00%
1	1	10.00%
1	3	10.00%
2	8	10.00%

I'm not sure I follow you, can you let me know what result you are expecting with the data you have provided?

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 19:06:30 GMT

The original file had an error in it, some of the values that said 50 were supposed to be 5. I had fixed the inline text, but forgot to reload before uploading.

The file now has the correct values, ones that match my original statement above. The results I'm looking for are as follows:

Sort first by Item, then by PercentSales Descending. Then Take Cumulative of PercentSales for each Item. Then if that value is <=80% count it

Item	Customer	PercentSales	Cumulative	<=80%?
1	2	25%	25%	1
1	4	20%	45%	1
1	5	20%	65%	1
1	1	10%	75%	1
1	3	10%	85%	0
1	6	5%	90%	0
1	7	5%	95%	0
1	8	5%	100%	0
2	7	25%	25%	1
2	5	20%	45%	1
2	6	20%	65%	1
2	8	10%	75%	1
2	1	5%	80%	1
2	2	5%	85%	0
2	3	5%	90%	0
2	4	5%	95%	0
2	9	5%	100%	0

Thanks

Re: CountIf CumulativeSum is < value.

Fri, 31 May 2013 19:07:29 GMT

If you reload the data in your file, it also has the correct inline text, and will automatically correct itself.

Re: CountIf CumulativeSum is < value.

RedSky001 — Sat, 01 Jun 2013 14:03:55 GMT

So I ended up doing this in the script as it was easier... (see attached file)

Re: CountIf CumulativeSum is < value.

Mon, 03 Jun 2013 13:07:18 GMT

That did it, thank you so much for your help!