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. I tried below mentioned two calculations but am not getting the desired result.
I’ve created a sample dashboard using Arena (YTD - Distinct GLs Shopped) using a sample dataset. Please provide inputs and help me implement this logic.
Also, these calculations should show the correct values when we change filters in the dashboard.
Hello @chabbils, I spent some time testing out alternative options for your calculations. Since there are only 3 months of data in the Quick Sight Arena analysis, I am not 100% certain the solution I wrote will work, but it is worth a try.
I think the core of the issue here is the calculation types that have to be nested while still ignoring the date filtering to retrieve the full YTD values. If the possible solution I created does not work, I think you will likely need to avoid filtering out dates if you want the YTD aggregations to function as expected.
That will be the best way to ensure the aggregations will run as anticipated and it will honestly simplify the calculations you need to write for this functionality. I’ll link the solution I wrote in Quick Sight Arena below:
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 Quick Sight Community!
@DylanM@Xclipse the solution is not correct and it didn’t resolve the issue. Looking at the screenshot, it seems like the YTD field is just doing a summation of the “GLs shopped” every month. For instance, the YTD value for Apr-2024 is “Mar-2024 + Apr-2024” and the YTD value for May-2024 is “Mar-2024 + Apr-2024 + May-2024” but it’s not giving me the YTD Distinct count of GLs because there are GLs which are common across months (e.g. see the second screenshot for Baby Registry), so those GLs should be counted only once while calculating YTD value.
Please take a look and advise what we can do further.
Hello @chabbils, I honestly do not have any further advice on partitioning the YTD by Gifting Experience and Month, while also ensuring the YTD is not impacted by date filtering on the visual.
I tried implementing some solutions by nesting a distinctCountOver in a sumOver, runningSum, and periodToDateSumOverTime so I could utilize the PRE_FILTER calculation level, but was unable to achieve the expected result.
Besides the issue of values being excluded when date filtering is applied, are you able to achieve the expected values for current year and last year when all months are shown on the pivot table? We can at least work towards the result when date values are not filtered from the visual.
Hello @chabbils, I wanted to check in since we have not heard back from you. If you could please provide some more information about the issue you are facing with your calculated fields, I can help guide you further. Otherwise, if we do not hear back from you in 3 days, I will archive this topic. Thank you!
Hello @chabbils, since we have not heard back from you with any further information or follow-up questions, I will archive this topic. If you still need assistance, please post a new question in the community and link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our Quick Sight experts. Thank you!
