Aggregated measures and periodtodatecount function

Question & Answer
, ,
1

I keep running into an error where I can’t use my aggregated function in a periodtodatecount measure.

I have a countif function built that has conditions I need included in my count. I want to compare this output to previous point in time YTD. The documentation I’ve read indicates periodToDateCount is the function I want, but it doesn’t allow me to use aggregated functions.
Here is the exact error: Nesting of aggregate functions like COUNT and COUNT(CASE WHEN “COALESCED_DATE” >= date_trunc(‘year’, ‘2024-08-02 19:45:19’::timestamp) AND “COALESCED_DATE” < ‘2024-08-02 19:45:19’::timestamp THEN COUNT(CASE WHEN “EVENT_TYPE” = _UTF16’engaged_lead’ AND “IS_ICP” = _UTF16’Is ICP’ THEN “EVENT_TYPE” ELSE NULL END) ELSE NULL END) is not allowed.

Here is my measure: periodToDateCount({ICP MAL Count}, {COALESCED_DATE}, YEAR)

I can get the periodtodateSum function to work just fine, provided I don’t use an aggregated function.

Is there a way around this?

Thanks!

2

Hello @jonfrank3366

Welcome to the community! Thanks for your question!

To solve this issue in Amazon QuickSight, you should avoid nesting aggregate functions. Instead, break down your calculations into separate steps. Here’s a step-by-step approach:

  1. Create a Calculated Field for the Count Condition: First, create a calculated field that includes your condition but without aggregation. This will serve as an intermediate step. Example:
IF(
    "COALESCED_DATE" >= date_trunc('year', '2024-08-02 19:45:19'::timestamp) AND
    "COALESCED_DATE" < '2024-08-02 19:45:19'::timestamp AND
    "EVENT_TYPE" = _UTF16'engaged_lead' AND
    "IS_ICP" = _UTF16'Is ICP',
    1,
    0
)
  1. Sum the Calculated Field: Create a new measure to sum up the intermediate calculated field. Example:
SUM(<Calculated Field from Step 1>)
  1. Apply the periodToDateCount Function: Use the periodToDateCount function on the summed measure. Example:
periodToDateCount(
    SUM(<Calculated Field from Step 1>),
    {COALESCED_DATE},
    YEAR
)

This way, you avoid the nesting of aggregate functions and can achieve the desired result. Here’s the summarized solution in steps:

  1. Create a Calculated Field:
ICP_MAL_Condition = IF(
    "COALESCED_DATE" >= date_trunc('year', '2024-08-02 19:45:19'::timestamp) AND
    "COALESCED_DATE" < '2024-08-02 19:45:19'::timestamp AND
    "EVENT_TYPE" = _UTF16'engaged_lead' AND
    "IS_ICP" = _UTF16'Is ICP',
    1,
    0
)
  1. Create a Summed Measure:
ICP_MAL_Count_Sum = SUM({ICP_MAL_Condition})
  1. Use periodToDateCount on the Summed Measure:
periodToDateCount({ICP_MAL_Count_Sum}, {COALESCED_DATE}, YEAR)

By splitting the logic into separate steps, you avoid the problem of nested aggregate functions and can achieve your desired comparison of YTD values.

1 Like
3

Thanks for the advice! That solution makes sense.