Skip to main content
Question

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

  • October 19, 2018
  • 0 replies
  • 68 views
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. 
      This topic has been closed for replies.

      • CasieMasters
      • Author
      • Quickbase Alumni
      • October 20, 2018
      Can anyone help me?
      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.
      • CasieMasters
      • Author
      • Quickbase Alumni
      • October 20, 2018
      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.  :(

      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.


        • CasieMasters
        • Author
        • Quickbase Alumni
        • October 23, 2018
        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?
        • anomDiebolt
        • Quickbase Alumni
        • October 23, 2018
        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'>"


        Terms & ConditionsAccessibility statement