Forum Discussion

CasieMasters's avatar
CasieMasters
Qrew Cadet
7 years ago

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

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. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.


  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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's avatar
    CasieMasters
    Qrew Cadet
    7 years ago
    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.  :(

  • CasieMasters's avatar
    CasieMasters
    Qrew Cadet
    7 years ago
    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?