Simple Solutions: Super Easy YOY Chart by MonthJan 25, 2022
A Year-Over-Year (YOY) chart can be critical to making sound business decisions, but you may feel like it's a "why-oh-why is it so hard to build in Quickbase" chart.
This type of chart shows a summary of data, such as sales, for several years overlapped on the same timeline so that you can easily compare performance from one year to the next.
And I get asked to make this chart A LOT!
Setting up a YOY chart in Quickbase is far from obvious, so I'll show you the steps.
Step #1: Identify the Data
For this type of chart, you need two data points from the same table.
- What is the 'amount' you want to show on the chart?
Typically this is something like Sales $, Expense $, or Quantity. It could also be the count of records, such as # of Opportunities entered.
- What is the 'date' you want to use in the report?
This may be the date a sale was made or when the opportunity closed.
Also consider possible data filters that may need to be applied to the report, such as Status, Type, or Category.
Step #2: Set up the Report
- From the table's homepage, click on "Reports & Charts", then on "New".
- Select "Chart" and click "Create"
- Give your chart a name and select "Line" type in the Chart Details
Step #3: Add a Report Formula
In order for the YOY chart to display properly, we need to extract the month from our reporting date.
Let's assume the date field for our report is "Order Date".
Quickbase has a few formula functions that will give us the Order Date's month:
- Month() - The numeric month value, such as 1.
- NameOfMonth() - The full name of the month, such as "January".
These functions can be added to a new formula field, but I prefer to use report formulas to avoid cluttering my table with fields that are just necessary for a particular report.
- Continue customizing the report by clicking on the "Add a report formula" button.
- Above the formula box that appears, enter the name for the formula ("Month" in our case), then select the formula type of "Numeric" and click on the green checkbox to save.
- In the formula box, enter the following formula with your date field name replacing "ORDER_DATE":
Step #4: Finish the Report Settings
Now that the month has been extracted, the remaining chart settings can be made.
- The "Category labels on x axis" should be set to your new "Month" report formula field.
TIP: Report formulas always show at the bottom of the field menu and not alphabetically with the table's fields.
- The "Data values on the primary y axis" should be set to your numeric field such as Sales or # of Opportunities.
- Skip the "Data values on secondary y axis" section.
- The "Series" should be set to your date field and grouped by year.
If you encounter an error message such as this before you can select Year, click OK, then choose Year.
- Make any final adjustments to your report to add filters, update the series colors for each year, or add a description (or name if you skipped it) and click on "Save".
Congrats! You now have your basic YOY chart!
OPTIONAL: "Fancy" Report Formula
I don't know about you but I really don't like to see 1, 2, 3,... in the x-axis of the chart.
I'd really rather see JAN, FEB, MAR,... my brain just absorbs the information faster.
For that, we will use a few more formula functions:
- Left() - Truncates a text string to the desired length leaving the leftmost characters.
- Upper() - Converts all text to upper case.
And the NameOfMonth() function mentioned earlier.
To make this update, first change the report formula type to "formula-text".
Next, enter this as the formula:
This will look great, HOWEVER, we've got an issue.
The months will display in alphabetical order APR, AUG, DEC,... not good!
To adjust for this, I add the 2 digit month before the month's name, using a few more functions. Note: 2 digits is necessary otherwise the text will order as 1, 10, 11,...
- PadLeft() - Adds a specified character to the left of a text string to achieve the desired length.
- ToText() - Converts non-text into a text string.
And the Month() function from the first example.
This will result in 01, 02, 03,...
Concatenating the two formulas with a dash in between, we get 01-JAN, 02-FEB, 03-MAR,... and they display in the proper order on the chart!
PadLeft(ToText(Month([ORDER_DATE])),2,"0") & "-" & Left(Upper(NameOfMonth([ORDER_DATE])),3)
I just LOVE having the ability to tweak my formulas to achieve exactly what I'm looking for!
This formula is great for other types of reports too!
Here I've added it to the column grouping in a summary report.
3 Types of Cumulative Sales Charts 3 Types of Cumulative Sales Charts
How to Add Charts to your Forms Watch the Video
Mastering the Text Formula Learn About the Course
Don't miss new Simple Solutions >> Subscribe Now!