Year-over-year metrics in a chart

I have been looking to find a way to illustrate year-over-year metrics using charts. Below is the data and chart I am looking for:

Screen Shot 2020-03-24 at 10.20.38 PM

Any suggestion on the best way to achieve this in Appsheets?


With a “col series [line]” chart, the X-axis will always show all rows of the table, labelled by the label-column.
And it allows you to select any number of columns to plot on the y-axis.

With that in mind, in order to display the kind of chart you want, your data would need organized like this:


You can keep your original table, and setup another table to gather the chart data in the right format by using SELECT() expressions.

Maybe not the best way to accomplish this, as I’m quite sure there are many solutions to this problem, but hope it helps.

Thank you for the feedback. I think this would work but one thing to mention why the data was setup as is, the customer requested to gather data for specific products as well. So really I should of posted multiple columns (see new screen grab). There is ~15 products to break out and a portion of the products would have a year-over-year graphs.

Screen Shot 2020-03-25 at 10.54.39 AM

So instead of breaking out each product into a new tab, I was hoping to have this data consolidated into one. In addition, the user then would only have to fill out one form to populate all data.

Upon 2nd review, I have a suggestion that is better than my first. It will accomplish the same thing, the only difference is how you would go about adding new years to the data.

Set up a new table like so:
With the year column a your label-column.
Use select() expression for each monthly column to pull the data from your original table, based on the year.

Then create a “row series [line]” chart view, and you enter all the monthly columns in the Chart columns section.

In my first suggestions, in order to add new years you would have to add new columns to the table, regenerate in the editor, and add a new expression for the new column, bad design!

In this new suggestion, all you need to do is add a new row to the table (in the app, not the editor) and specify the year.