Skip to main content
Question

%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

  • March 23, 2022
  • 1 reply
  • 7 views
.
Level 0

COUNT(CASE

WHEN 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")

    1 reply

    P
    Celonaut
    • pablo.maced
    • Celonaut
    • May 7, 2022

    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

     

    Community Code of ConductAccessibility statement