Forum Discussion

IvanWeiss's avatar
Qrew Captain
3 years ago

Cumulative Line Chart for Sales Data - New Formula Query's

Hi everyone,

I had created a complicated snapshot table  linked to my invoices table to generate a cumulative line chart showing sales YTD compared to last year.  It works, although not perfect.

With the new formula queries I am thinking this would now be simpler in a much easier and faster way with less moving parts.  I have had several quick base updates break my original concept (including recently with the move to pipelines) so rebuilding in query functions seems like a good idea.

Just not sure where to start here....  Has anyone had any success in doing anything like this that might shed some light on it?  Here is the workflow for where the data comes from:

Our invoices table is attached to a project.  When a client approves a project our team creates records on the invoices table to "forecast" when we will bill.  Ultimately once they invoice the client (in Quick Books) we enter the invoice # and date on the base record.  That is how we know the invoice went out vs a projection.

So what I need is a running total of the cumulative invoices to date based on the quick books invoice date field in the current year.  So 2019, 2020, and 2021 would have running sets of data in the same year.  Projection invoices would not count since their invoice date is blank (null).


Ivan Weiss

8 Replies

    • IvanWeiss's avatar
      Qrew Captain
      I went ahead and did this and now have successfully the running total by year on my table.  However, the next challenge is how to get it in a chart format.  Since it is a running chart I only need the "maximum" value for each month.

      So for example....

      January I have (3) invoices

      Invoice Date        Invoice Amount                Running Total
      01-02-21               $15,000                               $15,000
      01-06-21               $10,000                               $25,000
      01-15-21                $5,000                                 $30,000
      02-05-21               $10,000                               $40,000
      02-22-21               $5,000                                 $45,000

      In this example I cannot do a SUM by month as that would return 70,000 for January.  I need to grab the maximum record, the $30,000 and display that on a line chart for January.  And add the maximum of February making it 45,000 in February

      Ivan Weiss
      • MarkShnier__You's avatar
        Icon for Qrew Legend rankQrew Legend
        I suggest that you don not need to use Formula Queries for the chart.  Try using a Waterfall Chart type.

        Mark Shnier (YQC)