Cumulation

[terrorthijs]

I tried to cumulate the running totals for each week last year. Each entry was given 1 as teller, then I used

sum

(teller)/sum(total teller)

And then check cumulation, which gives me a nice graph. Now however, i want to know exactly in which week i've reached 90% of the total. I can read it out in the graph off course, but how do i calculate this?

See also the next link which holds the same question. The link with the solution however doesnt work anymore

http://community.qlik.com/forums/t/15050.aspx

[johnw]

I worked on something very similar to this before. Someone wanted to know how many customers made up the top 80% of sales, or something along those lines. I was able to produce a chart that had the count in it, but wasn't able to figure out how to make it a stand-alone expression.

Since this seems to come up now and again, I may keep trying to solve it. In the mean time, I'll at least share with you as far as I got with it, which is the attached example.

[Anonymous]

Take a look at the ParetoSelect in API, it may be close enough to what you need.

[terrorthijs]

I realise now the discription of my problem wasn't really accurate.

I've got some items in stock, and keep track of after how many weeks they are sold.

Using a variable as a reference (like 90% or so), i'd like to make a risk-analysis. With rangesum I can cumulate manually, however: i can't seem to find the point in the chart where both lines intersect automatically (thus giving me the number of weeks it takes to sell 90% of my items).

I'm not on a PC with QLikview right now, but i'll try to see if ParetoSelect will work (no experience with VB-scripting though), if it works i'll put an example online.

[terrorthijs]

ParetoSelect doesn't work, because it eliminates all data not within the first 90%, although i still need that 10% to make an estimation of profits and so forth.

Has anyone got another solution?

[johnw]

Well, it apparently wasn't what you were asking for, but please allow the indulgence of posting a follow up to my earlier post. I did get my approach working with a single expression rather than needing a chart. Here's the expression:

1 - sum(aggr(sum(if(Week<=N,Amount))/sum(total Amount)<.9,N))

As for N, I loaded it as an island field from Week, so it isn't connected to the data, and can thus be used simply as a counter for "looping" over all values of Week.

So at least I've satisfied my own curiosity. Now I'll need to take a look at what you ACTUALLY wanted to know instead of that. *chuckle*

(edit: Nope, I'm afraid I can't figure out what you're asking for, or at least how it differs in any noticeable way from what I was already thinking. You want a variable instead of a fixed 90%, which is a pretty trivial change. Other than that, I'm still not seeing it. Can you modify my example to demonstrate your actual situation, or post your own example of how your data is laid out?)