Count Unique Values YTD - New calculation for every year

Hello,

I am trying to do a distinct year-to-date count in quicksight calculations. For example for 2022, if a user has used a product every month of the year, the user should only be counted once. I’m using the below formula to get this unique count

runningSum(
distinct_count(
ifelse(
{Time Period}=minOver({Time Period}, [advertiserid], PRE_FILTER),
advertiserid,
NULL
)),
[{Time Period} ASC],

)

While this works, it does not partition by year. For Jan 2023, instead of starting a new unique YTD count, it carries over from December (included a screenshot of this)
Any ideas on how this formula can be modified to partition the calculation by year or anything else out there? TY

Also, when I add partition by year to the existing formula, it gives me only the new users for Jan 2023 so the number is understated due to the way the formula is. Happy to provide more details if needed.

runningSum(
distinct_count(
ifelse(
{Time Period}=minOver({Time Period}, [advertiserid], PRE_FILTER),
advertiserid,
NULL
)),
[{Time Period} ASC],
[truncDate('YYYY',{Time Period})]
)

Can you partition the running sum by year?

1 Like

Hi Max,

Thank you for this, however, it does not quite work. When I add the year partition, it starts afresh for the whole year i.e. gives me only new IDs for Jan (28) which isn’t what I want.

The formula seems to be working as expected when I include a filter for the data, i.e. filter for year 2022 and then for year 2023, the results are correct… See Image - the first visual is with the partition added. 2022 is correct but 2023 isn’t.

The second visual filters for date in 2023 and these results are what I’m looking to get.

I want these in the same visual rather than having to split each year in a separate visual.

When I don’t use the year partition, the first visual carries over the whole year of 2022 (See highlighted in yellow in the second image) which is not what I need.

I can include the dataset behind these IDs and the dates and the outputs for the formula if this helps better! TY!


Hi @Madura_Puri

I’m still a little unclear on the ask.

Have you looked into this post specifically on YTD calculations?

1 Like

Hi Max, Yes I have checked this, really helpful but unfortunately, I don’t think this applies to my use case.
I’m trying to calculate YTD monthly running distinct count

What about something like this.

periodToDateSumOverTime(runningSum(sumOver(distinct_count({session_id}),[{arrival_timestamp}]),[{arrival_timestamp} ASC],[truncDate('MM',{arrival_timestamp})]),{arrival_timestamp},YEAR)
1 Like

Thank you for this suggestion, @Max! I’ll try this. Do you know how this function works? I get a error saying contact quicksight team when I try to use it

periodToDateDistinctCountOverTime

Calculates running Distinct Count of a field, over the specified time period, partitioned by time period and all the group-by dimension

SYNTAX

periodToDateDistinctCountOverTime(aggregated unionset, date, period)

CC: @Max @Kristin @duncan

1 Like

Hello @Madura_Puri !

If you are still having trouble with this, could you try:
periodtodatedistinctcountovertime({advertiser_cfid},{report_day},MONTH)

If you need count({advertiser_cfid}), then I would try making that its own calculated field and then using it in place of the function in your full calculation.

1 Like