- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ABC Analysis in Qlikview
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi johnw, marcowedel,stalwar1 robert_mika swuehl, maxgro, kaushik.solanki marcus_sommer
Please find attached reference excel output file. Thanks
Best Regards, Deva
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share what you have right now? I would be eager to see the approach you have started?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This was recently introduced in QV12... you can read about it more here: Recipe for a Pareto Analysis – Revisited