Nested aggregate expressions in PQL


#1

Hello,

I created the following saved formula “PAYMENT_IN_DISCOUNT_PERIOD” in order to calculate the percentage of vendor invoices which were paid within the discount period:

SUM(CASE WHEN PROCESS EQUALS ‘Clear Invoice’ TO ANY TO ‘Cash Discount Due Date passed’ THEN 1.0 ELSE 0.0 END)
/
SUM(CASE WHEN “BSEG”.“ZBD1P” > 0 THEN 1.0 ELSE 0.0 END)

Now I want to display the company code (=dimension) with the highest rate (=KPI) in an OLAP table. In order to get this result, I used the following statement:

MAX(KPI(“PAYMENT_IN_DISCOUNT_PERIOD”))

I always get the error that an aggregate expression cannot be nested in another aggregate expression. I know that I have to bring the aggregation in my saved formula to another level with an alias, but how does this work in PQL?

Best regards
Thomas


#3

Hi Thomas,
You could simply sort decreasingly by the ratio and limit the OLAP table to one entry.
Best regards,
Pol


#4

Hello Pol,

I did that for the time being, but I want this OLAP table to always display the best company of this KPI as a benchmark. But when I do it as you described, then users can still sort ascending or descending or filter with the search function even if I disable the selections in the options of the OLAP table.

Best regards
Thomas


#7

Hi Thomas,

at the moment there’s unfortunately no other solution other than Pols suggested one.

Best regards,
Viana