Skip to main content
Question

How to get average of record count for 90 day period

  • January 11, 2023
  • 2 replies
  • 3 views
michael.chung
Level 1

Hi All,

I need some PQL coding help. I want to get a flat (or mostly flat) averge line for a 90 day period, but am not quite sure what I am missing.

 

output table format:

[created date] [id count] [90 day avg of id count]

 

OLAP table fields90 day Avg (PQL):

PU_COUNT(DOMAIN_TABLE("Account"."AccountNumber"), "Account"."Id", 

(CASE WHEN

 ROUND_DAY("Account"."CreatedDate") BETWEEN ROUND_DAY(TODAY())

 AND ROUND_DAY(ADD_DAYS ( ROUND_DAY(TODAY()), -90))

 THEN 1.0

 ELSE 0.0

 END

) = 1

) /90

 

Any suggestions on what to adjust?

Thank you

    2 replies

    G
    Level 12

    That PU_COUNT will return the number of Account ID's per each account number. That is, no a single value but a collection.

    So if the first value in that collection is 0, then you will get 0 as output.

     

    Maybe there is the problem as the filter seems ok.

     

    Also try to remove the filter to see if you get values. Just in case.

     

    HTH

    michael.chung
    Level 1
    • michael.chung
    • Author
    • Level 1
    • January 12, 2023

    That PU_COUNT will return the number of Account ID's per each account number. That is, no a single value but a collection.

    So if the first value in that collection is 0, then you will get 0 as output.

     

    Maybe there is the problem as the filter seems ok.

     

    Also try to remove the filter to see if you get values. Just in case.

     

    HTH

    Hi Guillermo,

     

    Thank you for the clarification.

    I tried without the filter and got values for 1.00 only.

     

    Anyway, I reworked my approach and this works for me.

     

    A. I put the time period filters at the table level.

     

    component filter:

    FILTER "Account"."CreatedDate" >= ADD_DAYS(TODAY(), -90);

     

    B. and then used Global values to calculate a fixed average for the data being viewed.

     

    kpis: (gives straight line avg for the table filtered time period)

    average over 90 day period:

    GLOBAL (COUNT("Account"."Id"))

    /90

     

    average of daily counts:

    GLOBAL (COUNT("Account"."Id"))

     / GLOBAL (COUNT(DISTINCT ROUND_DAY("Account"."CreatedDate")))

     

     

    Community Code of ConductAccessibility statement