Dynamic YTD Calculation depending on todays date for a count of Number of Events

Hi,

I’ve got a dataset that includes the number of events that happen in a year. For a year on year comparison I would like to only include the “Year to Date” data for every year depending on today’s date.

e.g. if Today’s date is 21st of August 2023. I would only want to include events that are in the dataset for every year that are before 21st of August 2022, 21st of August 2021, 21st of August 2020 etc.

I’ve included examples in the screenshots below to illustrate what I’m looking for:

Any help would be much appreciated for how to achieve this in Quicksight!

1 Like

1 Like

Hi Max,

I’ve made good progress on this as you can see in the screenshot below:

The main challenge I’m left with is that as you can see in the bar chart on the right is that I’m having to create an individual calculated field for each year. It would be preferred if I only had to create a single calculated field that would calculate the YTD for every year in the dataset.

I tried to look at “RunningSum” but not sure if that’s the right way to go here. Any help would be greatly appreciated!

Best
T

Hey @Tim_groot,

Can you share the calculated field you are using right now?

I have a few ideas in mind that might help with your bar chart.

Hi Max,

Thank you for offering to help.

I’ve created 3 calculated fields with the following calculations:

Events - Current YTD: periodToDateDistinctCount( {Event Name}, {Event Start Date}, ‘YEAR’, now())
Events - Previous YTD: periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’,addDateTime(-1, ‘YYYY’, now()))
Events - -2 YTD: periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’, addDateTime(-2, ‘YYYY’, now()))

The graph I’m able to make with the above calculated fields is for example:

As mentioned, I don’t want to make 3 individual calculated fields because this limits the number of graphs I can make to showcase the YoY trends, etc. It also means that if I wanted to show 3, 4 or 5 years of data I would have to make more calculated fields which isn’t scalable.

Any help would be greatly appreciated to be able to have one calculated field that automatically only takes YTD data for every year as opposed to having to make multiple calculated fields.

Tim

Hello @Tim_groot !

You can accomplish this via the calc below:

Ifelse(
periodToDateDistinctCount({Event Name}, {Event Start Date}, ‘YEAR’, now()),
'Events - Current YTD',
periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’,addDateTime(-1, ‘YYYY’, now())),
'Events - Previous YTD',
periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’, addDateTime(-2, ‘YYYY’, now())),
'Events - -2 YTD',
NULL)
1 Like

Thank you Duncan! Really appreciate the help!

1 Like