# Creating a daily sales report that also has a column for YTD cumulative values

• 0
• Question
• Updated 9 months ago
• In Progress
I have monthly report that summarizes GM \$\$ amounts by day.  I need to add a column that has the YTD GM total next to it and have it be the YTD for just that day.  So for instance if viewing April's reports, the YTD would only display YTD as of that day in April and not of the true current YTD amount as of Oct 18th.   I'm not sure how to create the field and have it summarized by the day.
• 292 Points

Posted 9 months ago

• 0
• 292 Points
Can anyone help me?
• 72,438 Points
OK, try this low tech solution

Make a summary report of GM \$\$ by Day and the days are listed vertically down the report.

Make a filter where date of sale is "during" the current year.

Have the report group rows by date (by day)
Then have it also summarize by day again, but this time select the option for running totals.
• 292 Points
Yeah, unfortunately I tried that and the report looks way to "busy" and Management balked and said they need it broken down by month.  My current reports are all broken down by Month and each quarter has it's own dashboard.  :(

• 72,438 Points
OK, plan B.
Make a table of dates imported from excel for all the dates in 2018, so 365 records.  We can worry about the other years later.

Set the key field to be the date.

Make a relationship back to your sales detail data based on the date field and then summarize up to the Dates table the sales for each day.  Call that field [Today's Sales]

Now comes the funky part.

Make a new relationship of the dates table to itself based on a formula date field called [Yesterday] with the formula of [date]-days(1)

Make a new  formula field called field called [YTD Sales] (blank formula for now).

Look up that field from that self referential relationship and call the lookup field [Sales YTD to Yesterday]

Set the formula for the field [YTD sales] to be [Sales YTD to Yesterday] + [Today's sales]

Now when you list any of the date records, (say for a month or a quarter) they can show the sales for that date as well as the YTD sales.

It's a daisy chain where each date looks up data from the previous date.

(Edited)
• 292 Points
I must not have done something right when I created the lookup field.  I'm not pulling in any figures.  I do have figures in my "Today's GM" though.  (I substituted Sales for GM), which are correct.

In the "GM YTD to Yesterday" field properties, my Reference field is Dates: Yesterday and the value field is Dates: YTD GM.  Is that correct?
• 72,226 Points
This is difficult to debug remotely.  Contact me off line if you like.  QuickBaseCoach.com

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
It is possible to create a cumulative column as demonstrated in my answer here using the field [Quantity]:

Cumulative column without summary report
` "<img src onerror='window.QBU_Sum = (window.QBU_Sum || 0) + " & [Quantity] & ";this.outerHTML=QBU_Sum'>"`