Year Comparison by Chart

[Domearian]

Not sure if this can be done but I'll ask in hope.  I want to visually compare 2 years of data via a chart. Now I have created 2 slices, 1 for this year and 1 for last year, that isn't a problem. Previous year was created using "(YEAR([Date])=YEAR(TODAY()-365))".

I can't see a way of comparing both slices on 1 chart and can't see a way of doing it. I have attached images of the difference in the data.Current YearPrevious Year

[Karimmc2]

Depending on what you want to achive there are many ways to do this. 

You could create a YEARS table and, using a virtual column to calculate the total value of each year, plot that table with the [Year] column as your X axis tag. Same can be done with a Month-Year. In case what you're looking for is a time series chart. 

If you don't mind the time series and you just want your data divided by year, you should use a [Year] virtual column in your main table and from ther you can agregate in different ways the values of each year (sum, count, average, etc. )

Or if non of this is what you are looking for, you can plot your data in a single chart and create a [Year] virtual column that is a reference to a YEARS Table, then in a single dashboard use a view of the years (Deck or table), and the view of your single Chart with the main Table. Then selec the dinamic dashboard option and with this your users will be able to filter the chart however they please. Just be sure that the main table has a reference to the Years table.

[Domearian]

The data is showing incidents recorded at what 'hour' over the course of the year. So have sliced the current year and previous year to this current year. 

I've create a virtual column which lists "Hour/Year". An example is an incident that has occurred at 2pm in 2021 the result is 14/2021 and an incident occurred at 2pm in 2022 will return a result of 14/2022, I get the results but that does not return the same sort of graphs as shown in previous post.

I want to display both years together so you can compare instead of using 2 separate graphs.

[Karimmc2]

You could, instead of using your main table and [Hour/Year] as a virtual column, use an Hour/Year Table that has a [Total_Incidents] virtual column that counts all the incidents during that hour on that specific year, and then plot this table using the hour and year as the chart columns and the [Total_Incidents] as the values. 

Additionally you can set a scheduled bot that each new year adds all the Hours records of the new year to the Hour/Year Table. 

Finally you could use a slice to filter only the values of the current and previous year on your Hour/Year Table if the only values you care about are the current and previous 

[Domearian]

The problem with that is creating another table. I would like to keep the number of tables used to a minimum.

[Karimmc2 · Accepted solution]

Hmm, I can think of some other solutions but using multiple virtual columns which would compromise the App's resources. I think adding one extra table would be the most efficient way of achieving this and this new table will have very few records so it should not impact your performance, neither the manage or user experience if you add the new rows to this table automatically in a yearly bot.

Or what is your main reason on not using an extra table?

[Domearian]

I have seen the light and am now using another table to get the desired results. 

[Karimmc2]

I’m glad it worked!