ABC: X% of suppliers do 80% of order value

ABC should work but with a combination of ABC + CASE WHEN

SUM(

CASE WHEN ABC ( "EKPO"."NETWR" ) = 1 THEN 1 ELSE 0 END

)

/ COUNT_TABLE(EKPO)

Hey @gabriel.okaba11, good to see you on this thread as well ;).

I am currently trying to setup a PIE chart that clusters vendors into A / B (with the ABC function distribution of 80 / 20 %.).

I could already realize the ABC analysis per vendor in an OLAP already with:

DIMENSION:

"AP_LFA1"."LIFNR" || ' - ' || "AP_LFA1"."NAME1"

and KPI:

FILTERED_ABC ( SUM ( "BSEG"."WRBTR_CONVERTED" ) , 0.8 , 0.15 )

-- i know that currently this shows 1 / 2 / 3 and not A / B but how the clustering with CASE WHEN statements is done is clear to me here.

Looks like this:

So far so good.

But how can I display the number of vendors that fall into the A / B / C clusters in a PIE chart? I.e. build a PIE chart that shows the three clusters A / B / C and for each cluster counts the number of vendors that are in it. Additionally, if e.g. we filter on a specific country, the calculation base should adjust accordingly.

Hope you can help out here.

Best,

Florian

Hi @gabriel.okaba11 @david.beche12 , quick update from my side on this OPEN QUESTION:

I could display the ABC Analysis as desired in a PIE chart with the following

Dimension:

CASE

 WHEN ( ABC ( PU_SUM ( AP_LFA1 , "BSEG"."WRBTR_CONVERTED" ) , 0.8 , 0.15 ) ) = 1 THEN 'A'

 WHEN ( ABC ( PU_SUM ( AP_LFA1 , "BSEG"."WRBTR_CONVERTED" ) , 0.8 , 0.15 ) ) = 2 THEN 'B'

 WHEN ( ABC ( PU_SUM ( AP_LFA1 , "BSEG"."WRBTR_CONVERTED" ) , 0.8 , 0.15 ) ) = 3 THEN 'C'

END

and KPI:

COUNT_TABLE(AP_LFA1)

The result:

So far so good. However the ABC clustering is not filtered when applying filters on the analysis unless I use FILTERED_ABC() as a function instead. Filtering is desired because we want to e.g. check the ABC clustering of vendors on a country specific level. Therefore, I would like to implement the filter possibility. When using the FILTERED_ABC() function however, it is not possible to select one cluster e.g. only the A cases from the PIE chart -> Celonis will return the following error:

I suppose that this is not possible because the FILTERED_ABC() function is basically stuck in an endless loop: First it clusters the cases of the current data scope into ABC, then when I try to filter on only A cases, the ABC function would re-calculate all cases for the ABC clustering but then cases for only A would change again and so on and so forth.

I have also tried to use FILTER_TO_NULL() for the net order value column but this ends up in the same error.

My question to you is: do you know of a way to implement the ABC clustering so that it is filtered with selections AND the possibility to only select one cluster of the ABC analysis, e.g. the A cases?. Maybe this is impossible with the ABC function...

Happy to have your input on this!

Best,

Florian

Hi Florian,

in general it is not allowed to filter on a result that takes filters into account. If that would be allowed, the order of filters would matter; but one feature of PQL is that filters can be applied in any order without changing the result. It's a bit unfortunate that you see this error when you try to filter; you shouldn't be allowed to click on a value at all. But in any case, it is not allowed to filter if you use a function that is filtered in the dimension query, e.g. an FILTERED_ABC or a FILTER_TO_NULL.

What you can do is to not use FILTERED_ABC, and instead "filter" based on dropdowns/variables, not based on selections. I.e. when you want to see the chart based on a country level, provide a dropdown and set the selected country code as a variable, and use this variable to write filter conditions inside your chart dimension (e.g. inside the PU function, or using a CASE WHEN inside the ABC function, depending on the lever you'd like to apply the filter).

Best

David