%AOI calc: no.open items/total avg. However when i remove the count function I am getting an error saying that aggregation function cannot be used together with dimension input. I only need the average and not the count of the average column. Th

Question

COUNT(CASEWHEN DAYS_BETWEEN(ROUND_DAY(<%= Earlier_Date %>),ROUND_DAY(CASE WHEN TO_TIMESTAMP('<%=date_to%> 23:59:59', 'YYYY-MM-DD HH:mm:SS') > TODAY()THEN TODAY()WHEN TO_TIMESTAMP('<%=date_to%> 23:59:59', 'YYYY-MM-DD HH:mm:SS') <= TODAY() THEN TO_TIMESTAMP('<%=date_to%> 23:59:59', 'YYYY-MM-DD HH:mm:SS')ELSE TODAY()END)) > 60 THEN "CASES"."_CASE_KEY" ELSE NULL END)/COUNT("Total Average"."Total")

pablo.maced · Answer

Hello Pradumma,Like it says on the error, it doesn't allow to use an aggregator and a dimension so you have two options:Change aggregation for a PU function, instead of using COUNT use PU_COUNT, in your case something like PU_COUNT(DOMAIN_TABLE("CASES"."_CASE_KEY"), CASE WHEN.......) should work for you.Uso 2 aggregators: change denominator and calculate the average instead if putting the dimension column that has that avg. Something like AVG(total). This might not be possible as I don't know what data is in that table and if the average can be calculated form other data.Hope this helps you solve the issue.For any further questions do not hesitate to ask.Best regards,Pablo

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN