Cannot count the number of group A customers, group B customers and group C customers

[davenyrfajutag00]

Hi everyone,

I am working on creating an "ABC Analysis" in Qlik.

I have a raw table where, on the left side, I have the customers' names (which I’ve removed from the picture).

From left to right, the table includes the following columns:

1. Sales amount
2. % Incidence
3. Cumulative % Incidence
4. Group assignment (A, B, or C).

I have created the following formulas for the raw table:

1. Customer ID                           
2. Sum(Sales)                                
3. Sum(Sales) / Sum(Total Sales)             
4. RANGESUM(ABOVE(Sum(Sales) / Sum(Total Sales), 0, ROWNO(TOTAL)))
5. IF(RANGESUM(ABOVE(Sum(Sales) / Sum(Total Sales), 0, ROWNO(TOTAL))) < 0.80, 'A', IF(RANGESUM(ABOVE(Sum(Sales) / Sum(Total Sales), 0, ROWNO(TOTAL))) < 0.95, 'B', 'C'))

Now, I would like to calculate the number of customers in each category (A, B, C). For example, in my case, there are 3 customers in category A, 6 in category B, and so on.

I want to perform this calculation in a KPI object, not within a row table object. I have already tried the following formula, but it doesn't work as expected:

COUNT(
AGGR(
IF(
RANGESUM(
ABOVE(
Sum(Sales) / Sum(Total Sales),
0, ROWNO(TOTAL)
) < 0.80,
'A',
CUSTOMER_ID
)
)

Thank you in advance for your help!

[EmmaMcAlister]

The ABOVE() function is evaluating the expression at the row above the current in the table. This won't work if you pull it out into a KPI object as there is no "row above" for it to look for.

Can you calculate the Group Assignment in your load script?
Then you could use set analysis to count within a KPI object.
COUNT({$<Group_Assignment={'A'}>}DISTINCT CUSTOMER_ID)

[davenyrfajutag00]

Thank you, Emma!

How would you calculate the group assignment in the load script?

If I calculate the group assignment in my load script, how would Qlik interpret the group assignment when a selection is made on the year? To be more specific, if a customer is assigned to Group A in the load script based on total sales over the years, will it change to Group B if a year is selected in the report, or will it remain fixed to Group A?

[EmmaMcAlister]

You can use GROUP BY and ORDER BY to create the table in the load script in the format that you need. 

What behaviour do you want on the front end - do you want it to be dynamic based on year selection or do you want the assignment to be fixed over the full term?

[davenyrfajutag00]

I would like it to be dynamic based on year selection.

So if the user is selecting 2022 or other years, this KPI should be recalculated based on the selections made and so on.

[BrandonFontes]

@davenyrfajutag00 

Try this

Sum(Aggr(IF(RANGESUM(ABOVE(Sum(Sales) / Sum(Total Sales), 0, ROWNO(TOTAL))) < 0.80, 1),[Customer ID]))

[hic]

Take a look at 

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684
https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-an-ABC-Analysis/ba-p/1472867

[EmmaMcAlister · Accepted solution]

I couldn't see a way to easily partition with a group by statement, so worked with each year separately.

Bear with me, it's a bit lengthy, so if anyone has a simpler solution, I'd love to hear about it!

// Inline table with customer ABC data for two years

CustomerABCData:

LOAD * INLINE [

    CustomerID, Year, Sales

    Customer1, 2022, 9456789

    Customer2, 2022, 5987654

    Customer3, 2022, 3876543

    Customer4, 2022, 2345678

    Customer5, 2022, 1234567

    Customer6, 2022, 987654

    Customer7, 2022, 765432

    Customer8, 2022, 654321

    Customer9, 2022, 543210

    Customer10, 2022, 432109

    Customer11, 2022, 321098

    Customer12, 2022, 210987

    Customer13, 2022, 109876

    Customer1, 2023, 10778594

    Customer2, 2023, 6565788

    Customer3, 2023, 4453030

    Customer4, 2023, 2430409

    Customer5, 2023, 1119256

    Customer6, 2023, 1014875

    Customer7, 2023, 804650

    Customer8, 2023, 623358

    Customer9, 2023, 426310

    Customer10, 2023, 358991

    Customer11, 2023, 204775

    Customer12, 2023, 187653

    Customer13, 2023, 147300

];

// Create a table of years with total sales

YearTotals:

LOAD

    Year,

    Sum(Sales) as YearTotalSales

RESIDENT CustomerABCData

GROUP BY Year;

// Create a list of unique years

TempYears:

LOAD DISTINCT

    Year

RESIDENT YearTotals;

Min_Max_Years:

LOAD

MIN(Year) AS MinYear,

    MAX(Year) AS MaxYear

RESIDENT TempYears;

LET vMinYear = PEEK('MinYear',0,'Min_Max_Years');

LET vMaxYear = PEEK('MaxYear',0,'Min_Max_Years');

DROP TABLE Min_Max_Years;

// Get the count of years

LET vYearCount = FieldValueCount('Year');

// Main loop to process each year's data

FOR i = 1 TO $(vYearCount)

    LET vYear = FieldValue('Year', $(i));

    // Create a temporary table for the current year with data ordered by sales

    TempYearData:

    LOAD

        CustomerID,

        Year,

        Sales,

        RecNo() as RowId

    RESIDENT CustomerABCData

    WHERE Year = $(vYear)

    ORDER BY Sales DESC;

    // Add the percentage calculation by joining with year totals

    LEFT JOIN (TempYearData)

    LOAD

        Year,

        YearTotalSales

    RESIDENT YearTotals;

    // Add SalesPercentage field 

TempYearDataWithPercentage:

LOAD 

CustomerID,

Year,

Sales,

RowId,

Sales / YearTotalSales as SalesPercentage 

RESIDENT TempYearData;

// Create a table with cumulative percentages 

TempCumulative:

LOAD 

CustomerID,

Year,

Sales,

RowId,

SalesPercentage,

RangeSum(Peek('CumulativePercentage', -1), SalesPercentage) as CumulativePercentage // Calculate cumulative percentage.

RESIDENT TempYearDataWithPercentage;

   // Create final ABC grouping for this year 

   [$(vYear)_ABC_Analysis]:

   NOCONCATENATE LOAD 

   CustomerID,

   Year,

   Sales,

   SalesPercentage,

   CumulativePercentage,

   If(CumulativePercentage < .80,'A',

  If(CumulativePercentage < .95,'B','C')) as GroupAssignment 

RESIDENT TempCumulative 

   ORDER BY Sales DESC;

   // Clean up temporary tables 

   DROP TABLES TempYearData , TempYearDataWithPercentage , TempCumulative;

NEXT i;

// Now concatenate all the year-specific tables 

// Initialize ABC_Analysis with the first year 

ABC_Analysis: 

NOCONCATENATE LOAD * RESIDENT [$(vMinYear)_ABC_Analysis];

// Loop through the remaining years to concatenate 

For vYear = $(vMinYear)+1 to $(vMaxYear) 

   CONCATENATE (ABC_Analysis) 

   LOAD * RESIDENT [$(vYear)_ABC_Analysis]; 

   DROP TABLE [$(vYear)_ABC_Analysis]; 

NEXT vYear 

// Clean up remaining tables 

DROP TABLES CustomerABCData , [$(vMinYear)_ABC_Analysis], TempYears , YearTotals;

Final result:

Let me know how you get on!

[davenyrfajutag00]

Thank you so much Emma!

I will try and let you know!

[davenyrfajutag00]

I have just applied your script structure to my case.

Your solution is the one I have been looking for!

Thank you Emma!