I am struggling to use ABC function. it is not giving expected results.

Can anyone help here.

Regards,

Sachin

I am struggling to use ABC function. it is not giving expected results.

Can anyone help here.

Regards,

Sachin

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

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

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

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

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

Hi Kerstin,

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

regards,

Sachin

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

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

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

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