topic Count with specific threshold in QlikView

Count with specific threshold

Wed, 16 Nov 2011 10:27:10 GMT

Hi to all,

i need, if is possible, you help.

In the file db_bank1 i have 3 variable. Bank (A or B) , User (name of user), Euro (money).

I need a expression that for each bank:

- Calculate 40% of Total Euro=Thershold

- Cumulate euro of user (descending, from higher import of euro) and count how many user are under the threshold of 40% of Total euro of each bank.

I hope that you can uderstand my question. Explain in English is not simple...

Ask me for more details.

Thank YOU!!

Marco

Wed, 16 Nov 2011 11:05:45 GMT

Hi Marco,

if i understand your problem, you can try this expression:

count({$<user={"=Sum(euro)<(Sum(total <bank> euro)*0.4)"}>}user)

C u,

Stefano.

[update.] Sorry this works only if u select a Bank.

swuehl — Wed, 16 Nov 2011 11:17:55 GMT

Hi Marco,

try

=count(aggr(if(sum(euro)<0.4*sum(total<bank> euro),user),bank,user))

I noticed that 40% is quite a high threshold in your example data set, no users are above that limit?

Regards,

Stefan

Wed, 16 Nov 2011 12:03:38 GMT

Hi,

mybe i didn't explain well my request.

The threshold of 40% is to compare with the sum of the first higher users.

In other words: i need to know how may user altogether are under the threshold of 40%.

In the Ex: for bank A are 2.

If you sum the 2 most high euro you are a little bit under 40% of euro bank A. (12269).

Thank You

swuehl — Wed, 16 Nov 2011 12:17:32 GMT

Ah, you want something like a pareto count, right?

Hm, I think that there are some limitations, a good discussion thread is this:

Stefan