ABC analysis with condition

[Abdrakhimov]

Hello team. I'm trying to do a variable filter based abc analysis. I have a table: Factory, Year, SKU, sales. At the moment, abc analysis of SKU-sales only works for me. How to make so that the boundaries of this analysis are based on the other two dimensions, which will be chosen by the user in the diagram. (User picks factory or year and ABC analysis change) Aggr( If(Rangesum(Above(Sum({1} [Sales])/Sum({1} total [Sales]),1,RowNo()))<0.8, 'A', If(Rangesum(Above(Sum({1} [Sales])/Sum({1} total [Sales]),1,RowNo()))<0.95, 'B','C')), ([SKU], (= Sum({1} [Sales]) ,Desc) ) )

[sunny_talwar]

Would you be able to share a sample to show what you have and what exactly are you looking to do?

[Abdrakhimov]

Thanks for paying attention Sunny. Here is my option, I applied different formulas, but nothing works. I am trying to do an ABC analysis so that the areas of this analysis change with the filter. In other words, the user selects the factory and the analysis is conducted within the framework of.

[sunny_talwar]

Can you provide an example from the data provided? For example, if I select South, what isn't right? and what should it look like?

[Abdrakhimov]

Good day Sunny. I have 4 factory's: South, East, West, North . Every factory have 6 SKU (product). Each factory has its own sales for each SKU (Sales). I want to do an ABC analysis, but that the decomposition was in the field of the selected factory from the filter. Example in xls file. I will be glad to any ideas, thank you for your attention Sunny!

[sunny_talwar]

Hi

So, I loaded the data you provided and when I select Western, I saw this

This looks similar to what you want. Is it not? What is not right? Can you pinpoint it in the above image?

[Abdrakhimov]

Hello Sunny. In this picture I showed how it should work. Here abc analysis is done by hand, what I'm trying to do in qlik. Next to the column with your formula. If you select a factory from the filter, the formula works perfectly. As you can see, the formula does not work if the filter is not used at all. Any ideas on how to solve this problem? I will welcome any advice.

[sunny_talwar · Accepted solution]

Try this expression

=Aggr(
If(Rangesum(Above(Sum(Sales)/Sum(TOTAL <factory> Sales), 0, RowNo())) < 0.8, 'A',
If(Rangesum(Above(Sum(Sales)/Sum(TOTAL <factory> Sales), 0, RowNo())) < 0.95, 'B', 'C'))
, factory, (SKU,(=Sum(Sales), Desc)))

[Abdrakhimov]

Your formula a little shows a little not the result that I need. The calculation is made in the dimensions of each factory separately, but it gives incorrect results. If you pay attention to the sorting of the SALES column, you will immediately see that the ABC categories are incorrectly assigned. Nevertheless, your formula will help me a lot to find a solution to this problem, thanks Sunny.

[sunny_talwar · Accepted solution]

Give this a shot

=Aggr(
If(Rangesum(Above(Sum(Sales)/Sum(TOTAL <factory> Sales), 0, RowNo())) < 0.8, 'A',
If(Rangesum(Above(Sum(Sales)/Sum(TOTAL <factory> Sales), 0, RowNo())) < 0.95, 'B', 'C'))
, factory, (factorySKUKey, (=Sum(Sales), desc)))

Where factorySKUKey is created in the script like this

[Лист1]:
LOAD [factory],
	 [SKU],
     [factory]&'|'&[SKU] as [factorySKUKey],
	 [Sales]
 FROM [lib://Desktop/Example for community.xlsx]
(ooxml, embedded labels, table is [Factory sales]);