Discussions

Expand all | Collapse all

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

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

    Posted 10-18-2018 23:18
    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. 


  • 2.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-19-2018 20:07
    Can anyone help me?


  • 3.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-19-2018 20:16
    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.


  • 4.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-19-2018 20:24
    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.  :(



  • 5.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-19-2018 20:38
    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.




  • 6.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-22-2018 20:21
    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?


  • 7.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Posted 10-22-2018 22:52
    This is difficult to debug remotely.  Contact me off line if you like.  QuickBaseCoach.com


  • 8.  RE: Creating a daily sales report that also has a column for YTD cumulative values

    Top
    Contributor
    Posted 10-22-2018 20:27
    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'>"