Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
Master II

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)

ABC Current output_1NOV2016.JPG

ABC_Analysis_Expected output.jpg

31 Replies
ciampaciampa
Contributor III
Contributor III

Great! It works!

Thanks

Not applicable

Amazing dimension, I've been looking for this for days . Thanks!

bohravanraj
Partner
Partner

Hi Sunny,

Thanks for you solution.
I have to view this ABC analysis at Shop No level.

can you please suggest any solution for this.

iIworked on this but i m facing issue in sorting in Aggr at Shopno and Article No level.

Any help is appreciated.

Thanks,

Vanraj Dinesh Bohra

sunny_talwar

Would you be able to share a sample where you have been doing this? and explain your required output?

bohravanraj
Partner
Partner

Hi Sunny,

Thanks for your Prompt Reply

Dimension which i m using is :

=if(Aggr(sum(Aggr(RangeSum(Above(TOTAL Sum(Sales), 0,RowNo())) / sum(TOTAL <ShopNo,Week> Sales),ShopNo,(ArticleNo,(=Sum(Sales),DESC)))),ShopNo,ArticleNo) >0.90,'C',

if(Aggr(sum(Aggr(RangeSum(Above(TOTAL Sum(Sales), 0,RowNo())) / sum(TOTAL <ShopNo,Week> Sales),ShopNo,(ArticleNo,(=Sum(Sales),DESC)))),ShopNo,ArticleNo) >0.60,'B','A'))

My Output should be in this Format:   

GradeShop 1Shop 2Total
A Stocks145071123425741
B Stocks749712128709
C Stocks24803432823
Total244841278937273

I figured out problem for above calculated dimension is we need sorting by Shopno and for Each shop by article number in descending order.

Regards,

Vanraj Dinesh Bohra

sunny_talwar

Oh nice... so you got what you wanted, right?

bohravanraj
Partner
Partner

No Sunny,

my problem is output is not coming right because its unable to sort the data properly in Aggr Function at following level:

1. It should sort by Shopno.

2. for each shop it should sort articleno in descending order.

This is what i m trying to achieve with above calculated dimension, but still not found any success.

Do you have any solution for this.

Regards,

Vanraj Dinesh Bohra.

sunny_talwar

Try this

=Aggr(

If(RangeSum(Above(TOTAL Sum(Sales), 0, RowNo()))/Sum(TOTAL <ShopNo, Week> Sales) > 0.9, 'C',

If(RangeSum(Above(TOTAL Sum(Sales), 0, RowNo()))/Sum(TOTAL <ShopNo, Week> Sales) > 0.6, 'B', 'A')),

ShopNo, (ArticleNo, (=Sum(Sales), DESC)))

joan
Contributor
Contributor

Hello Sunny,

Do you know how could I do to create a KPI for the 'A Stocks' or the 'B Stocks' without using a table?

I think I need to use set analysis but I don't know if that is possible.

For example:

sum ( {< Master dimension created = {'B Stocks'} >} TotalNetPrice)

What I don't know is what to type in "Master dimension created" so that it calculates it properly...

I would appreciate any help!

Thank you in advance,

Joan

tejal92
Contributor
Contributor

hi,

abc analysis is not working for INV_RATE= INV_Value / INV_Stock