Cumulative sum without charts

teempi — Fri, 20 Jan 2012 10:25:08 GMT

Hello,

I'm having a hard time calculating a cumulative sum without using charts (with above()). The case is roughly this:

I have sales data by customer (let's forget the other dimensions for now) which I need to process. I've made a chart that has 3 colums: customerid, customer ranking (based on sum(sales)) and cumulative percentage of sales (meaning that the percentage of the last customer = 100%). The cumulative column uses above() function which is okey in a chart.

Here's an example:

Customer ID	Sales Ranking	Cumulative Sales %
13	1	0.15
3	2	0.29
2	3	0.42
7	4	0.54
5	5	0.65
9	6	0.75
10	7	0.85
1	8	0.90
6	9	0.94
4	10	0.96
15	11	0.98
14	12	0.99
8	13	0.99
11	14	0.99
12	15	1.00

The real question is this: how can I extract a specific customer id based on the cumulative percentage? Let's say I want to have a text box that has the id of the customer that is the first one to have a percentage greater than 0.5 (meaning customer 7). How do I do this?

I've managed to do this with a macro but that's one extra click the user has to make.

Any help is much appreciated!

-Teemu

topic Cumulative sum without charts in QlikView

Cumulative sum without charts