Step #1: Identify the Data

For this type of chart, you need two data points from the same table.

1. 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.
2. 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

1. From the table's homepage, click on "Reports & Charts", then on "New".
   
    
   
   
2. Select "Chart" and click "Create"
   
   
   
   
3. 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.

1. Continue customizing the report by clicking on the "Add a report formula" button.
   
   
   
2. 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.
   
   
   
3. In the formula box, enter the following formula with your date field name replacing "ORDER_DATE":

month([ORDER_DATE])

 Step #4: Finish the Report Settings

Now that the month has been extracted, the remaining chart settings can be made.

1. 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.
   
   
   
   
2. The "Data values on the primary y axis" should be set to your numeric field such as Sales or # of Opportunities.
   
   
   
   
3. Skip the "Data values on secondary y axis" section.
   
   
4. 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.
   
   
   
   
5. 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:

Left(Upper(NameOfMonth([ORDER_DATE])),3)

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,...

PadLeft(ToText(Month([ORDER_DATE])),2,"0")

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!

Learn more about functions like Left, Upper, and PadLeft.

This formula is great for other types of reports too!

Here I've added it to the column grouping in a summary report.

 More Resources

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!