I would like to do a dropdown but I keep getting a message that "The aggregation function SUM cannot be used toether with dimension function input. I tried making saved formulas for my code, but also I've got error.Have you got idea how to do it?

Hi Julia,

can you please provide me with the exact formula you used with the sum Function, so I could understand your issue and hopefully I could help you solve it ?

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END))<0 THEN 

ABS(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

-

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END)) 

ELSE

(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

-

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END)) END) BETWEEN 0 AND 10 THEN 'OK'

WHEN 

(CASE WHEN

(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

-

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END))<0 THEN 

ABS(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

-

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END)) 

ELSE

(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

-

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END)) END)>11 THEN 'NOT OK'

END

I showed the code without writing down the formulas to make it easier to see what I was counting. If you want with saved formulas I'll change it.

Thank for helping

Am I missing something? because i can't see any SUM function, only COUNTs.

As you are using the same syntax, let's first focus on the first COUNT function: so you are trying to count the number of cases with this condition ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY()) and as the count function ignores null values i can understand that even the condition is met the column "BSEG"."WRBTR_CONVERTED" could have null values, so your goal is, if i get it right to count the cases with ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY()) and "BSEG"."WRBTR_CONVERTED" not null ?

Yes, I'dont want count null.

that way you can change the formula to : SUM ( CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY()) AND  "BSEG"."WRBTR_CONVERTED" IS NOT NULL THEN 1 ELSE 0 END)

try to implement this syntax for all the COUNTs and that should work without aggregation errors.

Let me know if it works fine :)

However, I'm not sure we understand each other correctly. I would need to make a filter where I can choose 'ok' or 'not ok'.

ok would be if the code I sent above equals 0 and if different from 0 then 'not ok'.

I was thinking about this kind of filter.

yes sure but to use this Dropdown Filter we need a right pql code syntax, so i just wanted to point out directly where the problem is.

For example just try this part of pql code as a KPI :

COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") = ROUND_MONTH(TODAY())

THEN "BSEG"."WRBTR_CONVERTED"

END))<0 THEN 

ABS(COUNT(CASE WHEN ROUND_MONTH("BSEG"."AUGDT") BETWEEN ADD_MONTHS(ROUND_MONTH(TODAY()),-3) AND TODAY()

THEN "LFA1"."LIFNR" END)/3

if you get the same error so i understood your problem correctly (Syntax error), if you get a number so the problem is different

Just let me know

I've got number

but in filter I see

I think, Your KPI is aggregating the lines but dropdown option only works in case level

Have you got idea how I can do it in diffrent way?

Sorry based on formula, i didn't understand requirement.

Hi Julia,

this is a very specific use case that needs more depth Knowledge on your Data especially how your Case Table is structured, to give you the right solution. the dropdown selection should give the cases with the condition ok or not ok