How to show cumulative current year and previous year running sum and include in the same line graph

[kiko]

Hi, I have a dataset like this:

I want to calculate the cumulative sum of completion column by month and compare the value with the same month last year in a line chart, like the graph below:

Blue line is the cumulative completion for the current year and the pink line is the cumulative completion for the previous year.

Pink line is calculated as periodOverPeriodLastValue({Cumulative Completions}, {event_date}, MONTH, 12). {Cumulative Completions} is calculated as runningSum(sum({completion}),[{event_date} ASC]) I only want to show the current year date, in the example above - Jan 2023-Dec 2023. If I only select Jan 2023-Dec 2023, then periodOverPeriodLastValue doesn’t show any value in the graph.

I tried below methods too:

1. ifelse(dateDiff({event_date},today(),“YYYY”) = 1, {Cumulative Completions}, 0). However, this shows error of mismatched aggregation.
2. I also tried the windowsum windowSum(sum({completion}), [{event_date} ASC], 0, 12) but the result doesn’t seem correct.

Is there any other way to calculate the cumulative completion for the previous year and to show in the same graph? Thanks in advance!

[DylanM]

Hello @kiko, this is definitely a fun challenge. I have spent some time trying to figure out the best way to do this as well and I will try to explain the solution that I have utilized in the past.

First thing I want to note though, on this method that you tried ifelse(dateDiff({event_date},today(),“YYYY”) = 1, {Cumulative Completions}, 0), if you switched today() for now(), you might resolve the error, but I am assuming that is likely related to other fields in the calculation, but it is worth a try.

Now, the way I have gone about displaying 2 years side by side, is creating an ifelse statement that will create category values that you will use in the color field well. Basically check your date field to see if it equals this year or last year, and return something like Current Year and Previous Year as strings. Then, to handle your x-axis, you will basically want to extract your dates to pull only the month value. This will allow you to show them month over month together with the x-axis displaying 1-12 instead of Jan, Feb, Mar, etc. Although, you may be able to extract the month name if you play around with it rather than just the number.

Then for your values, you will want to use an ifelse to figure out if your date field is within this year or last year, and return your values that exist within the this year/last year time constraints.

I will link the documentation below for the extract function, because that will be your best friend when building out the date portion of the visual.

Extract - Amazon QuickSight

I will mark this as the solution, but if you have any follow-up questions on this topic, please let me know. Implementing this can definitely be a bit tricky and I will do my best to help!

[kiko]

Hi @DylanM Thank you for your answer!

ifelse(dateDiff({event_date}, now(),“YYYY”) = 1, {Cumulative Completions}, 0) doesn’t work either because the error of Mismatched Aggregation is on the {Cumulative Completions} field. This calculation works when I want to calculate completion YoY difference, but not cumulative completions.

For the second part, could you be more specific? Just to clarify, I only want to display the cumulative completion for 2023 and 2022 separately in one line chart, not side by side. periodOverPeriodLastValue couldn’t calculate any value if I only select one year period.

Thanks!

[DylanM]

Hello @kiko, in regards to your mismatched aggregation error for your ifelse returning cumulative completions or 0, what if you changed this to returned the event_date or NULL instead and then used that calculated field in your cumulative completions aggregation. That would ensure you will avoid the mismatched aggregation error for an unaggregated date field with a running sum/periodOverPeriod aggregated field.

As for the 2nd point I was discussing, I can try to explain this more in-depth. Something you can try would be to create a calculated field that would return a string value to group the 2 years of data. You can do something like this:
This field will be your color field well

yearName = ifelse(dateDiff({event_date}, now(), 'YYYY') = 1, "Last Year",
dateDiff({event_date}, now(), 'YYYY') = 0, "This Year",
NULL)

Then you could try utilizing the addDateTime function, this will allow you to keep your dates in the date formatted display, to check if the date is last year, and then convert it to a date for this year.

addDateTime - Amazon QuickSight

It would look something like this:
This will be your x-axis field well

mergeDates = ifelse(dateDiff({event_date}, now(), 'YYYY') = 1, addDateTime(1, 'YYYY', {event_date}),
dateDiff({event_date}, now(), 'YYYY') = 0, {event_date},
NULL)

Now you need to return the data for each year that will be grouped by the color field well:
y-axis field well

mergedData = ifelse(dateDiff({event_date}, now(), 'YYYY') = 1, {Cumulative Completions},
dateDiff({event_date}, now(), 'YYYY') = 0, {Cumulative Completions},
NULL)

In my local testing for this, I didn’t have to filter NULL values. But if they display for you, you can filter on one of the calculated fields you created, and use the custom filter with the null values dropdown set to exclude NULLs. This should give you the display you are expecting, so I will mark it as a solution. If you have any follow-up questions on this topic, please let me know.