topic Re: Pareto calculation in QlikView

Pareto calculation

karin_nevo — Thu, 24 May 2018 10:43:38 GMT

HI,

I am trying to get the total suppliers with 80% of the purchase orders amount.

I was able to do it in a straight table, by using the following expression:

= sum ( aggr ( If ( Rangesum ( Above ( sum ( Amount) ,0 , Rowno() )) / sum ( Total Amount ) <= 0.8, 1 , 0) , BP ))

This expression works if the table is sorted by purcahse order amount in a desc order.

But, I want to use this in a text object to display only the count of suppliers with 80% of the purchases.

If I use the same expression in text object it doesn't display the correct result becasue there is no sort.

I know in QV 12 there is a solution for it in AGGR fucntion, but I am using QV11.

Thanks,

Karin

Re: Pareto calculation

jmvilaplanap — Thu, 24 May 2018 10:57:26 GMT

Hi,

Try to use the Rank function

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/RankingFunctions/rank.htm

Re: Pareto calculation

sunny_talwar — Thu, 24 May 2018 10:58:58 GMT

Check here

Pareto calculation

Re: Pareto calculation

karin_nevo — Thu, 24 May 2018 11:00:50 GMT

this example uses the sorting option in AGGR fucntion that is only avaialbe in QV 12

Re: Pareto calculation

karin_nevo — Thu, 24 May 2018 11:01:52 GMT

I tried to use it and couldn't get what I needed. Can you send me the expression of how to use it?

Re: Pareto calculation

sunny_talwar — Thu, 24 May 2018 11:02:41 GMT

Sorry, I missed the last line... but for QV11.2... you can check here

ABC Analysis in Qlikview

Re: Pareto calculation

karin_nevo — Thu, 24 May 2018 12:30:03 GMT

Thanks Sunny.

I didn't find a solution on the post of how to get the number in text object (and not in chart).

I also don't want to use buttons to get the result only on button click

am I missing something?

Re: Pareto calculation

sunny_talwar — Thu, 24 May 2018 12:37:44 GMT

Hate to say, but QV11.2 solutions are going to be very restrictive. Instead of using button, you can use triggers based on selections... but then you go into the territory of unknowns as to what might trigger when you select something. I am sure you don't like my response, but unless you move to QV12, there is no easy way to do this in QV11.2

Re: Pareto calculation

karin_nevo — Thu, 24 May 2018 12:53:49 GMT

I understand, thanks for all your help

Re: Pareto calculation

sunny_talwar — Thu, 24 May 2018 13:02:17 GMT

no problem at all