YTD Distinct Count and YTD-YoY% Distinct Count

[chabbils]

Hi all,

I want to calculate YTD distinct count of GLs used for shopping (by the customers) and YTD-YoY% for the same but I am not able to perform this calculation. Please provide inputs to help me implement this.

Sharing the dummy data here:

[Brett]

Hi @chabbils,
For the YTD, you could use a periodToDateSumOverTime calculation

Year To Date=
periodToDateSumOverTime(sum({GLs shopped}), Reporting Date, MONTH)

Then for YTD-YOY%, you should be able to use something like this

Let me know if you have any additional questions or if this works for your case.

[Brett]

Hi @chabbils,
It’s been awhile since we last heard from you so just wanted to check in and see if you had any additional questions or if you were able to solve your question?

If we do not hear back within 3 days, I’ll go ahead and mark as solved.

Thank you!

[chabbils]

Hi @Brett, the calculations are not working correctly. The thing is I want the YTD distinct count (not YTD count). Same goes with YTD-YoY%. The attached screenshot shows the result I got based on below mentioned calculated fields I created. Please help me get the correct calculations.

• GLs shopped: distinct_count({gl_product_group_desc})
• YTD (distinct GLs shopped): periodToDateSumOverTime(distinct_count({gl_product_group_desc}), {reporting_date}, YEAR)
• YTD-YoY% (of distinct GLs shopped): periodOverPeriodPercentDifference({YTD - GLs shopped}, {reporting_date}, YEAR, 1)

I also tried below calculation for YTD Distinct count but it’s not giving correct result:

• YTD (distinct GLs shopped): periodToDateSumOverTime(distinct_count({gl_product_group_desc}), {reporting_date}, MONTH)

[Xclipse]

Hi @chabbils, does this post help:

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 …

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

[chabbils]

@Xclipse I tried the solution from older post but it didn’t resolve the issue.

I’ve created a new post (YTD Distinct Count & YTD-YoY% Distinct Count) and created a sample dashboard using Arena YTD - Distinct GLs Shopped

[Xclipse]

Great! Thanks, we will follow-up on the other post.