Threshold values on PO line item amount

Hi all,

we’re using threshold values based on PO header level in order to filter on low or high value items (e.g. below 100€ or above 10k€). The formula works on PO header level (EKKO table) but should be based on line item level (EKPO) - the formula looks as follows:

CASE WHEN PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) >= 0 AND PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) <= 100 THEN ‘a) 0 - 100€’ WHEN PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) > 100 AND PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) <= 500 THEN ‘b) 100 - 500€’ WHEN PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) > 500 AND PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) <= 1000 THEN ‘c) 500 - 1.000€’ WHEN PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) > 1000 AND PU_SUM(“EKKO”,“EKPO”.“NETWR_CONVERTED”) <= 10000 THEN ‘d) 1.000 - 10.000€’ ELSE ‘e) > 10.000€’ END

I tried to exchange EKKO with EKPO to have ‘PU_SUM(“EKPO”,“EKPO”.“NETWR_CONVERTED”)…’ but that didn’t work. Any suggestions how to change properly?

Thanks,
Oli

Hi Oli,

when you want to calculate the formula on a item level, you don’t need to use the PU function since the NETWR_CONVERTED is already on item (EKPO) level.
So you could use the following formula:

CASE WHEN SUM(“EKPO”.“NETWR_CONVERTED”) >= 0 AND SUM(“EKPO”.“NETWR_CONVERTED”) <= 100 THEN ‘a) 0 - 100€’ WHEN SUM(“EKPO”.“NETWR_CONVERTED”) > 100 AND SUM(“EKPO”.“NETWR_CONVERTED”) <= 500 THEN ‘b) 100 - 500€’ WHEN SUM(“EKPO”.“NETWR_CONVERTED”) > 500 AND SUM(“EKPO”.“NETWR_CONVERTED”) <= 1000 THEN ‘c) 500 - 1.000€’ WHEN SUM(“EKPO”.“NETWR_CONVERTED”) > 1000 AND SUM(“EKPO”.“NETWR_CONVERTED”) <= 10000 THEN ‘d) 1.000 - 10.000€’ ELSE ‘e) > 10.000€’ END

Best regards,
Viana

1 Like

Thanks! Since we use this formula via button drop downs and OLAP tables I had to adjust also the titles and drop downs accordingly, but it worked all out with the formula provided (even w/o using SUM at all). Thanks :smile: