How to make a Year-Over-Year Line Chart in QuickSight

Question

Hello,

I need help to understand if there is a way to display year-over-year data in a line chart.  For my particular example, I am plotting vehicle rentals over time.  I want to plot the date the rental occurs on the x-axis and the count of total rentals for that day on the y-axis.  The tricky part is that I want to show the data with a year-over-year comparison and that is where I need help.

I am aware of the Color field for Line Charts that allows grouping different lines over one another by different dimensions.  However, I cannot do this because I am already using the date field along the x-axis...QuickSight tells me "You can't use the same field for more than one dimension (blue) field well.  Choose different fields for them"

Essentially what I want is for the x-axis to show all the months (or days) from January to December.  Then Each different color line will represent the count of rentals for each year. Ideally, this would be drillable too, so a user could drill down to the week or day view but preserve the year-over-year view. Here is an image that I've created demonstrating this: [YoY Line Chart](https://drive.google.com/file/d/1YmNVkl9yKNjrYvtyOqP6ZTs7VAiJhOsA/view?usp=sharing)

I have come up with a workaround by creating a calculated field to extract each part of the date (year, month, date, etc) so that QuickSight doesn't scold me for using the same field in multiple dimension field wells.  The problem with this is that then those fields are no longer of the DateTime type.  I don't like this because I lose the value of using Dates in a Line chart (relative date filtering, automatic drilling, etc.) and, if it isn't a date, QuickSight can only sort alphabetically (April comes before January, etc.)

Does anyone have a technique for showing YoY trend data in the way I've described?

Thanks

Answer

Hi ,

you could potentially use the [periodOverPeriodLastValue](https://docs.aws.amazon.com/quicksight/latest/user/periodOverPeriodLastValue-function.html) to create a calculated field for PreviousYear for the specific measure, or more than one calculated field if you want to compare more years, and play with the offset.

using your method, instead of splitting all part of the years, you could just create a calculated field that maps the same day and month to an non existent year in your data set ( i.e. calculated field `parseDate(concat('1900-',substring(toString(Date),6,5)),'yyyy-MM-dd') `) .
In this way you have stiil a date format and you can use the visual custom fromat for the date to show MMM-dd i.e. Jan 01.

obviously the first method should be the right one to test the second is more of a workaround.

Answer

Thank you for posting your question, as of now the two ways you can achieve what you have described are the one you mentioned in your post and the one mentioned by Fabrizio@AWS above.

How to make a Year-Over-Year Line Chart in QuickSight

Relevant content