Calculating Accumulated Sales with Quickbase

If you are in finance or sales, you eventually will ask how you can see your sales figures in an accumulated way.

We have all seen charts that display ever increasing sales performance with the arrow going up and to the right. How do you do that in Quickbase? Read on and view the video tutorial included below.

Quickbase is a live database and so when you look at a month like December you will see the actual December sales numbers. What you don’t see is the accumulated sales total of January + February + March, etc. One way of doing this is to roll your sales numbers by their month up to a Years table using summary fields. i.e. “Show me all the sales where the month is equal to 1.” (Did I tell you we created a field in the child table that looks at the date and gives us the month’s number i.e., Month([Date])? We can use that when we create a summary field.) When the fields for each month are present in the Years table you can then create a formula for each month and add the previous months to calculate its respective month to date.

Summaries by month, total them and send them down as lookups

For example, a field calculating the accumulated total for March might be named Jan+Feb+Mar with the formula being [Jan]+[Feb]+[Mar]. You would create twelve of these so you have an ever increasing sales volume over time. Next, you want to pass all twelve of these formulas down to the child table as look up fields and figure out how each month knows its own Accumulated Sales ($) to date.

Since every record has all twelve of the months accumulated totals, we need another formula numeric field to figure out which of the accumulated values to use. We can now use the [Month] field and if it’s 1 then use the [Jan] field. If it is 2 then use the [Jan]+[Feb] field and if 3 then [Jan]+[Feb]+[Mar], etc.

The first formula called [Month] is simple. The formula looks at the date of the child record and extracts the month it is in, Month([Date]) and with this number (1,2,3,4…etc.) we have a formula tell us which of the twelve lookup fields should represent our accumulated sales thus far. The Accumulated Sales ($) field’s formula might look like this:

Case([Month],
1,[Jan],
2,[Jan+Feb],
3,[Jan+Feb+Mar],
4,[Jan+Feb+Mar+Apr],
5,[Jan+Feb+Mar+Apr+May],
6,[Jan+Feb+Mar+Apr+May+Jun],
7,[Jan+Feb+Mar+Apr+May+Jun+Jul],
8,[Jan+Feb+Mar+Apr+May+Jun+Jul+Aug],
9,[Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep],
10,[Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct],
11,[Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov],
12,[Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec])

In the end each monthly record has its own month’s numbers as well as the accumulated months preceding it for that month’s, Accumulated Sales ($). We can now create a chart report in the Monthly Sales table and trend sales performance over time, where one line is the monthly sales and the other line is that ever increasing line that moves up and to the right.

We talk about things like this in our daily office hours. All are welcome to attend and you are welcome to ask any question you like.