Filter using agregation

Dear team,
We have an indicator related vendors who has been paid with both, MM and FI invoices and we calculate this as a ratio, so far so good.
The problem is when we want to provide the list of those vendors for further review, We have the dimension “Vendor” and one calculated column that states whether the vendor has FI and MM invoices or either FI or MM which we understand is not subject to review and its listed as “OK”

as the vendor has both FI and MM is calculated as an aggregation. (See formula below) I can not implement a filter just to show only those that are ‘FI + MM’ , in the screenshot below i would like to exclude those that are “OK”

Do you have any suggestion?
All the best ,


CASE
WHEN
SUM( CASE WHEN “BKPF”.“AWTYP” != ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0 AND
SUM( CASE WHEN “BKPF”.“AWTYP” = ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0
THEN ‘FI + MM’
ELSE ‘OK’
END

1 Like

Hi Christian,

as far as I get your issue correctly, you want to apply a filter that filters out all cases that are ‘OK’. In this case you can simply apply a component filter as follows:

FILTER
CASE
WHEN
SUM( CASE WHEN “BKPF”.“AWTYP” != ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0 AND
SUM( CASE WHEN “BKPF”.“AWTYP” = ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0
THEN ‘FI + MM’
ELSE ‘OK’
END
= ‘FI + MM’

(I assume that the CASE WHEN statement works fine in your analysis. Otherwise please let me know)

I hope that it’ll solve your issue!

Best,

Justin

Hi Justin,
Thanks a lot for your reply, The solution proposed does not work, i tried that on the past, I believe the reason is that I’m using aggregation to calculate the categories.

Hi Cristian,

instead of a regular SUM, you need to use PU_SUM.
In the first argument of the PU function you need to specify the grouping. I’m not familiar with your data model; it’s either the vendor table or a DOMAIN_TABLE with the dimensions you have in your OLAP table. The second argument of the PU_SUM is the part that you already have in your SUM.

So the filter would look sth like this. If this doesnt work or it returns a wrong result, please show the queries of the dimensions of your olap table.

FILTER
CASE
WHEN
PU_SUM( "Vendors", CASE WHEN “BKPF”.“AWTYP” != ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0 AND
PU_SUM( "Vendors", CASE WHEN “BKPF”.“AWTYP” = ‘RMRP’ AND “BSEG”.“BSCHL” = ‘31’ THEN 1.0 ELSE 0.0 END ) > 0
THEN ‘FI + MM’
ELSE ‘OK’
END
= ‘FI + MM’

Best
David

1 Like

Dear David,
Thanks a lot for your help, I changed my formulas and the filters using pullout functions and now its working as desired. I need to start thinking more on using aggregations !
All the best