How to create YTD value with Month(string) Relative date control?

Question & Answer
, ,
1

Hi Team,
Currently, I create YTD for sales using below calculated field, I Need to update YTD with Quarter level control? How to add the relative date YTD by string?

Note:Quarter control is calculated field
For Example: * Example: YTD= 2024 Q1 ,YTD would be from 01/01/2024 - 03/31/2024

YTD:ifelse(dateDiff({Date},now(),“YYYY”) = 0 AND {Date}<=now(), {Sales}, 0)

Quarter:concat (‘Q’ , toString( extract ( ‘Q’, addDateTime(0, ‘MM’, truncDate(‘MM’, {Date})))) , ’ ', toString(extract(‘YYYY’, addDateTime(0, ‘MM’, truncDate(‘MM’, {Date})) )) )

image

1 Like
2

Hello @AdithyanDurairajan, welcome to the QuickSight community!

What is happening when you apply the filter as is? It should filter out the data you don’t want and still run the calculated field. I am assuming the main issue is due to you checking for the year based on now() but only have dropdown value options for 2023. That would mean none of your data will show.

Maybe instead of your current YTD calculated field, you could use a sumOver aggregation with the PRE_AGG operand to run the calculation after the filter has been selected.

Year Date = truncDate('YYYY', {Date})
YTD Sales = sumOver({Sales}, [{Year Date}], PRE_AGG)

Now, if you exclude to just the data from Q4 2023, it should provide you with that total value. One thing to note with LAC-W aggregations like sumOver, you will want to set it as a min aggregation in your field well. That will ensure the value is not repeated or summed multiple times. Let me know if that helps!

3

Hello @AdithyanDurairajan, since we have not heard back from you with any follow-up questions, I will mark my previous response as the solution. Please let me know if you have any remaining questions and I can help guide you further. Thank you!