topic Re: ABC Analysis in Qlikview in QlikView

ABC Analysis in Qlikview

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to build ABC report for Sales analysis. having issue with calculated dimension(Grade) and based on user selection it should show bellow attached expected output screen shot, currently i'm getting wrong results (only the ABC summary report not correct result). if you have any solutions/idea's much appreciated for your help. thanks

Note: used below script for calculated dimension (Grade)

Cumulative%:

LOAD *,TotalNetPrice/TotalSales as SalVale%,

If(RangeSum(Peek('run_sum'), TotalNetPrice/TotalSales) < 0.6, 'A Stocks',

If((RangeSum(Peek('run_sum'), TotalNetPrice/TotalSales)) > 0.6 and (RangeSum(Peek('run_sum'), TotalNetPrice/TotalSales)) < 0.9 , 'B Stocks', 'C Stocks')) as Grade,

RangeSum(Peek('run_sum'), TotalNetPrice/TotalSales) as run_sum

Resident Sales

Order By TotalNetPrice desc;

FYI

Current output (refer the ABC summary report)

Re: ABC Analysis in Qlikview

marcus_sommer — Tue, 01 Nov 2016 12:50:47 GMT

I'm not quite sure how do you want to classify your articles but if you do it within the script it couldn't be depend on selections anymore - maybe it would be sufficient if you include more dimensions like country and category within your aggregation-load and on which you react in the following peek-load.

But I think you will rather need a calculated dimension within the gui - like described here: Calculated Dimensions and an approach like the following:

Recipe for an ABC Analysis

ABC analisys. As it should be...

Segmentation and custom dimension grouping

ABC Analysis to set and remember classification at runtime

I have adapted a chart from the first blog-posting which is based on a ranking and a second one (the left one) which based on the sales and which should be already quite near to your expected output except for the wrong sorting which will be quite difficult to create with a qlikview release prior to QV 12 - but with them it should be easy: The sortable Aggr function is finally here!‌

- Marcus

Re: ABC Analysis in Qlikview

devarasu07 — Tue, 01 Nov 2016 17:42:50 GMT

Hi Marcus,

Thank you very much for your help, in my requirement is to calculate the grade based on calculated measure and user selected dynamic filter value.

Let's assume we have list box filter as country,Category,Week,size etc, Based on the dimension we need to calculate the Grade (Calculated dimension). would it be possible to derive below summary report by Grade

problem statement:

The problem with calculated dimension is that unable to sort the RangeSum in the descending order and it's creating incorrect Grades.

Please refer below screen image,

Best Regards,

Deva

Re: ABC Analysis in Qlikview

marcus_sommer — Thu, 03 Nov 2016 13:01:43 GMT

Have you tried my suggestion with a sortable aggr-function in QV 12 (maybe at first within a local installation)?

- Marcus

Re: ABC Analysis in Qlikview

devarasu07 — Thu, 03 Nov 2016 16:21:39 GMT

Hi Marcus,

Yes, I've tried with the suggested aggregated with sorting option as below,

=Aggr(if(

rangesum(above(SUM((TotalNetPrice)/SUM(TOTAL TotalNetPrice)),0,RowNo(Total)))<0.6,'A Stocks',if(

rangesum(above(TOTAL SUM((TotalNetPrice)/SUM(TOTAL TotalNetPrice)),0,RowNo(Total)))>0.6 and

rangesum(above(TOTAL SUM((TotalNetPrice)/SUM(TOTAL TotalNetPrice)),0,RowNo(Total)))<0.9,'B Stocks',

'C Stocks'

)

),ArticleNo, (TotalNetPrice ,(NUMERIC,DESCENDING)))

But still could not yield the desired output. I have attached the current and expected output. Please share your valuable suggestion/solution for this.

Note:

I would like to have below kind functionality where i would like to sort by expression. Basically a ORDER BY.

Aggr(sum(sales),(sum(sales),(NUMERIC, ASCENDING)))

This will open up lot of possibilities. One good example is Running Total/cumulative total as Calculated Dimension.

Thank you once again.

Best Regards, Deva

Re: ABC Analysis in Qlikview

marcus_sommer — Fri, 04 Nov 2016 10:34:24 GMT

If I look on the syntax from the new aggr-function I don't think that an expression as sorting-attribute is possible. I haven't a QV 12 available so I couldn't test anything. Perhaps there is a possibility by extending the condition for a ranking-function maybe by nesting them within another aggr-function might work. For this I would like to invite swuehl to this posting who had a lot experience with aggr-functions or he might give useful hints for a different approach.

- Marcus

Re: ABC Analysis in Qlikview

sunny_talwar — Fri, 04 Nov 2016 15:59:11 GMT

As I mentioned to you before also, I think troyansky's approach is probably the only approach (AFAIK) available, I have created a sample for you using the approach he mentioned here: So How Many Customers Make Up Most of Your Sales?

The only problem is that you are not including the boundary conditions, but Pareto Select includes it. So when you say Sum(TotalNetPrice) of less than 50%, your chart shows 2 ArticleNo, but pareto includes the anything until it crosses the 50% mark. May be someone (marcus_sommer‌, Oleg, or someone else) might be able to help you take this even forward.

Oh and forgot to mention that every time you change your selections, you will need to click on Recalculate to make this work. Other method is to add the button actions as OnAnySelect action, but that could drastically slow down your dashboard. But it will definitely help you get rid of the recalculate button

Re: ABC Analysis in Qlikview

devarasu07 — Sat, 05 Nov 2016 05:09:03 GMT

Hi Sunny,

Thank you so much for the solution provided. As you said, i'm now facing with one issue. When using Pareto select, the percentage for StockA is 60. Say, 2 stocks are filtered out. The second pareto select's percentage is 90. What now happens is, this time it includes the StockA's 2 article count and also the articles which are less than 90%. When it comes to StockC, the similar thing happens. Is there anyway we can mention the percentage range instead of only the minimum value. Legends of qlikview - marcus_sommer, swuehl. Please pen down your valuable suggestion for this.

Thanks a lot in advance ! Have a wonderful weekend !

Regards, Deva

Re: ABC Analysis in Qlikview

devarasu07 — Mon, 07 Nov 2016 16:03:54 GMT

Hi stalwar1 robert_mika‌ swuehl‌, maxgro, kaushik.solanki‌

Can you help me to advise on this Pareto Percentage (%) range selection issue. Thanks

need to set like below,

StockA: <=60%

StockB: >60 and <=90%

StockC: >90%

Regards,

Deva

Re: ABC Analysis in Qlikview

sunny_talwar — Tue, 08 Nov 2016 02:21:56 GMT

I am out of ideas on how we can take this forward, lets get some other experts involved may be johnw‌, marcowedel‌

Re: ABC Analysis in Qlikview

devarasu07 — Tue, 08 Nov 2016 02:53:49 GMT

Hi johnw, marcowedel,stalwar1 robert_mika swuehl, maxgro, kaushik.solanki marcus_sommer ‌

Please find attached reference excel output file. Thanks

Best Regards, Deva

Re: ABC Analysis in Qlikview

johnw — Tue, 08 Nov 2016 19:42:55 GMT

After the pareto selects, I'm thinking add toggle select steps using firstsortedvalue() to pull the lowest one out of StockA and put it in StockB, and the lowest from StockB and put it in StockC. I'm getting inconsistent results, though.

Re: ABC Analysis in Qlikview

sunny_talwar — Tue, 08 Nov 2016 19:44:50 GMT

Can you share what you have right now? I would be eager to see the approach you have started?

Re: ABC Analysis in Qlikview

johnw — Tue, 08 Nov 2016 20:19:26 GMT

I don't have a very good understanding of firstsortedvalue(), though that part seems fine. What seems to be happening is that the steps in the action list are not executing in order, and my firstsortedvalue() is referencing a previous state of the alternate state. I'm able to get the intended results by moving the toggle selects to a "Fix" button, and manually pressing the two in order. Surely I'm missing something basic. Anyway, here's what I have. Press the two buttons in sequence, and it generates the right report. I suppose it could be done with one button and a macro, where you can be explicit about the sequence of steps, but that would be annoying.

Re: ABC Analysis in Qlikview

sunny_talwar — Wed, 14 Dec 2016 22:06:25 GMT

Just what you needed:

Recipe for a Pareto Analysis – Revisited

Now you don't need to create buttons to make this work. The Qlik have brought the ability to sort using an expression within the aggregate function. This will make your life so much more easier.

Best,

Sunny

Re: ABC Analysis in Qlikview

devarasu07 — Thu, 15 Dec 2016 00:53:25 GMT

Hi Sunny,

Thanks for the update, currently I'm using qlik 11.2 x version (serverand) & my client desktop 12v. do I need to upgrade to latest version to make this bug fix will work.? Thanks

Regards,deva

Re: ABC Analysis in Qlikview

sunny_talwar — Thu, 15 Dec 2016 02:04:17 GMT

Not sure which version might have this functionality, but you would at least need QV 12 or higher.... Working on updating the sample to check if it actually works. I am very excited to test this out.

Best,

Sunny

Re: ABC Analysis in Qlikview

sunny_talwar — Thu, 15 Dec 2016 02:17:38 GMT

Here is the new chart

Dimension

=Aggr(If(

rangesum(above(TOTAL (Sum({<ArticleNo>}TotalNetPrice)/Sum(TOTAL {<ArticleNo>} TotalNetPrice)),0,RowNo(Total)))<0.6,'A Stocks',if(

rangesum(above(TOTAL (Sum({<ArticleNo>}TotalNetPrice)/Sum(TOTAL {<ArticleNo>} TotalNetPrice)),0,RowNo(Total)))>0.6 and rangesum(above(TOTAL (Sum({<ArticleNo>}TotalNetPrice)/Sum(TOTAL {<ArticleNo>} TotalNetPrice)),0,RowNo(Total)))<0.9,'B Stocks',

'C Stocks'

)

), (ArticleNo, (=Sum({<ArticleNo>}TotalNetPrice), DESC)))

Expression

=Count(DISTINCT ArticleNo)

=Count(DISTINCT ArticleNo)/Count(TOTAL DISTINCT ArticleNo)

=Sum(TotalNetPrice)

=Sum(TotalNetPrice)/Sum(TOTAL TotalNetPrice)

Re: ABC Analysis in Qlikview

ciampaciampa — Wed, 08 Mar 2017 14:58:12 GMT

Sunny,

I want to use a measure or a calculated dimension as a "order by" parameter in the Aggr().

Reading your solution it seems possible. (ArticleNo, (=Sum({<ArticleNo>}TotalNetPrice), DESC)).

Am I wrong? Please could you confirm me?

Thanks a lot in advance

Regards

Re: ABC Analysis in Qlikview

sunny_talwar — Wed, 08 Mar 2017 16:30:01 GMT

This was recently introduced in QV12... you can read about it more here: Recipe for a Pareto Analysis – Revisited