My inbox has been flooded with requests for this information!

Whether it's sales $, hours worked, or customers entered – being able to see cumulative totals month over month can be an invaluable management tool.

However traditional Quickbase reports only allow records to be counted once.

So if you have a sale in January, it can't also be counted in February (let alone the rest of the year).

In order to get around this issue, I'll share my method for creating cumulative charts in Quickbase using formula queries.

In the video, I walk you through how to build not one, but three types of cumulative sales charts (of course you don't have to use them just for sales).

1. All Time Cumulative Sales
2. YTD Sales by Month (starts at 9:11, modifying from report #1)
3. YOY Cumulative Sales (starts at 11:46, modifying from report #2)

While the charts demonstrated here focus on reports grouped by month-of-year, similar reports could be set up to group by week-of-year or day-of-month. It would just be a matter of adjusting the formula, chart setup, and filters to match the desired groupings.

These charts are sure to be a big hit! Enjoy

WORDS OF CAUTION

• This strategy is best suited for more advanced Quickbase builders.
• If you are not familiar with formula queries, watch these videos first:
  Query Writing 101 for Quickbase
  Oh So Fantastic Formula Queries
• If you are filtering your chart (by status, customer, date, or any other field), those same filters need to be included in the query formula.
• Dynamic report filters will not properly adjust the totals shown in these reports (because they don't affect the query formula). I suggest turning them off to avoid confusion.
• If your tables have hundreds of thousands of records, the formula queries may not calculate.

Be sure to reference the Formulas below for easy copy/paste and the Resources to download the same dataset from the video if you want to follow along.

 All Time Cumulative Sales

Chart Report Setup:

Chart: Bar

Color: ffc800

Category Labels on x axis: ORDER_DATE     Group by: Month

Data values on y axis: Cumulative Sales*     Summarize by: (averaged)

Alternative:
Data values on y axis: Cumulative Sales (2)*     Summarize by:  (summed)

Report Formulas:

*Cumulative Sales - Numeric-Currency

var text QUERY = "{10.OAF.'01-01-2020'}AND{10.OBF.'" & LastDayOfMonth([ORDER_DATE]) & "'}AND{11.EX.'Shipped'}";

SumValues(GetRecords($QUERY),27)

Alternative: Sums values into a single record vs all records
*Cumulative Sales (2)  - Numeric-Currency

var text QONE = "{10.OAF.'01-01-2020'}AND{10.OBF.'" & LastDayOfMonth([ORDER_DATE]) & "'}AND{11.EX.'Shipped'}";
var text QTWO = "{10.OAF.'" & FirstDayOfMonth([ORDER_DATE]) & "'}AND{10.OBF.'" & LastDayOfMonth([ORDER_DATE]) & "'}AND{3.GTE.'" & [Record ID#] & "'}AND{11.EX.'Shipped'}";

If(Nz(Size(GetRecords($QTWO)),0)=1, SumValues(GetRecords($QONE),27),0)

Filters:

STATUS is equal to the value Shipped

Include a date filter if looking back to a specific period (vs using the full dataset).

YTD Sales by Month

Chart Report Setup:

Chart: Line & Bar

Colors: bb5beb, 4ce3eb

Category Labels on x axis: ORDER_DATE     Group by: Month

Data values on y axis: Cumulative Sales*     Summarize by: (averaged)

Bar values on secondary y axis: SALES     Summarize by: (summed)

Report Formula:

*Cumulative Sales - Numeric-Currency

var text QUERY = "{10.OAF.'" & FirstDayOfYear([ORDER_DATE]) & "'}AND{10.OBF.'" & LastDayOfMonth([ORDER_DATE]) & "'}AND{11.EX.'Shipped'}";

SumValues(GetRecords($QUERY),27)

Filters:

STATUS is equal to the value Shipped

and ORDER_DATE is during the current year

 YOY Cumulative Sales

Chart Report Setup:

Chart: Line

Colors: 11f015, 4ce3eb, bb5beb

Category Labels on x axis: Month*     Group by: Equal Values

Data values on y axis: Cumulative Sales*     Summarize by: (averaged)

Series: ORDER_DATE     Group by: Year

Report Formulas:

*Cumulative Sales - Numeric-Currency

var text QUERY = "{10.OAF.'" & FirstDayOfYear([ORDER_DATE]) & "'}AND{10.OBF.'" & LastDayOfMonth([ORDER_DATE]) & "'}AND{11.EX.'Shipped'}";

SumValues(GetRecords($QUERY),27)

*Month - Text     (see Super Easy YOY Chart for more details)

PadLeft(ToText(Month([ORDER_DATE])),2,"0") & "-" & Left(Upper(NameOfMonth([ORDER_DATE])),3)

Filters:

STATUS is equal to the value Shipped

 RESOURCES:

Sales Transactions:

• CSV File Download

Query Writing:

• Query Writing 101 for Quickbase
• Oh So Fantastic Formula Queries
• Quickbase documentation

Formula Writing Courses:

• Mastering the Text Formula
• HTML Formula Crash Course
• Formula Bundle

Feeling like a Junkie?  Subscribe Now!