ABC New Function

frontend

#1

I am struggling to use ABC function. it is not giving expected results.
Can anyone help here.

Regards,
Sachin


#3

Hi Sachin,

could you explain to us in more detail why the ABC-Function is not working as expected?
Which results do you expect?

Best regards,
Kerstin


#4

Kerstin,

Total spend is 20.81 b, if i categorise A spend as 70% then i expect 70% of 20.81b to be categorized under A.
where as i see just 9.655b under A.

Am i missing something/

Regards,
Sachin


#5

Hi Sachin,

okay thanks, I understand the problem!
Could you provide me with the PQL-formula calculation for

  • KPI PO Spend
  • KPI Spend in thet table
  • KPI Spend (m€) in the table
  • and KPI # Parent

Best regards,
Kerstin


ABC function filtering the results
#6

Hi Kerstin,
Thank you for looking into this.
As requested, sharing below the formulas used.

PO Spend =
(SUM(“SCM.Tables::_CEL_SCM_CASES”.“POItemValueEUR”)/1000000000)
KPI spend in table =
(SUM(“SCM.Tables::_CEL_SCM_CASES”.“POItemValueEUR”)/1000000)
ABC Formula =
(CASE WHEN ABC(“SCM.Tables::_CEL_SCM_CASES”.“POItemValueEUR”,.7,.2) = 1 THEN ‘A’
WHEN ABC(“SCM.Tables::_CEL_SCM_CASES”.“POItemValueEUR”,.7,.2) = 2 THEN ‘B’
ELSE ‘C’ END)
Parent Supp =
(COUNT(DISTINCT “SCM.Views::_CEL_SCM_PO_ITEM”.“PARENT_SUPPLIER_NAME”))

Regards,
Sachin


#7

Hi Sachin,

which relationship does the table “SCM.Views::_CEL_SCM_PO_ITEM” have with your case table?
Aggregation functions (like the ABC Operator) sometimes deliver unexpected results when there is a 1-to-many relationship between the case table and another table in the datamodel.

Best regards,
Kerstin


#8

Hi Kerstin,

Table “SCM.Views::_CEL_SCM_PO_ITEM” has one to one relationship with case table.

regards,
Sachin


#9

Hi Sachin,

you might have to calculate the total Purchase Order Spend for each vendor first using a Pull-function and then apply the ABC-Operator in order to receive correct results.
However, it is easer to tell if I know about the relationship between the tables you are accessing. Could you provide me with a screenshot of you datamodel?

Best,
Kerstin


#10

Hi Kerstin,

I observed that when i remove component filters numbers are exactly what i expect(70% in A, 20% in B and 10 % in C). The moment i apply component filters, the percentages does not match.

Do you help me fix this?

Regards,
Sachin


#11

Hi Sachin,

can you try the same calculation using the FILTERED_ABC Operator?
Filtered means that the results of your calculation are updated with set filters e.g. vendors could be assigned to a different category.

Best,
Kerstin


#12

Filtered_ABC worked. :smiley::smiley:

Thank you Kerstin for your help.

Regards,
Sachin


#13

Hi Kerstin,

Sorry, re-opening the discussion again.
Now as the total spend is matching with the ABC distribution.
But one supplier is appearing under A, B and C. it is because of the spend we have is under PO lines. So under A top 70 percent PO line spent is appearing. but the same supplier is there is lower value of a PO line and hence appearing in C category.

How do i get the supplier count under A,B and C, when i have spend column under each PO line.

Let me know in case you have any question.

Regards,
sachin


#14

Hi Sachin,

could you send a screenshot of the datamodel?

Best,
Kerstin