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
