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

  • 0
  • 1
  • Question
  • Updated 2 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. 
Photo of Casie Masters

Casie Masters

  • 292 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Casie Masters

Casie Masters

  • 292 Points 250 badge 2x thumb
Can anyone help me?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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.
Photo of Casie Masters

Casie Masters

  • 292 Points 250 badge 2x thumb
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.  :(

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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)
Photo of Casie Masters

Casie Masters

  • 292 Points 250 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,350 Points 50k badge 2x thumb
This is difficult to debug remotely.  Contact me off line if you like.  QuickBaseCoach.com
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
It is possible to create a cumulative column as demonstrated in my answer here using the field [Quantity]:

Cumulative column without summary report
https://community.quickbase.com/quickbase/topics/cumulative-column-without-summary-report#reply_1912...

 "<img src onerror='window.QBU_Sum = (window.QBU_Sum || 0) + " & [Quantity] & ";this.outerHTML=QBU_Sum'>"