Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
5 years ago

Cumulative Sales Line Chart

Hi everyone,

I reached out to Quick Base support but unfortunately got the most ridiculous no effort answer ever :(. So disappointed in that honestly as they are normally helpful.  I assume checked out for the holidays.  But, I am hoping someone here can help me.

I have an app that maintains a billing section of the app under a larger Project Manage Scope.  The organization is as follows:

Projects < Opportunities < Orders < Invoices

What I am trying to accomplish is a line chart that over three years can show me a comparison snapshot by month of cumulative billing.  I want two charts:

One chart showing the data as Total Invoices.  So it compares monthly and cumulative.  So the data point for January needs to be January, February is January + February, and March is January + Feb + Mar etc.

The second chart is going to do the exact same thing but filter the data on a "type" field in the Opportunities table.  That is already carried all the way down to the invoices table via lookups so the data is present already.

Here is how far I got.....Two issues persist:

  1. The data is not cumulative.  It is a summary of the total sales per month.  I need to make it cumulative.
  2. Less of an issue, but how do I sort the months properly?  As you can see it is running January, October, November, December since it is a numeric field grabbing the date of billing in a month format.

Also FYI we only joined Quick Base somewhat recently so the sales that are 0 are correct as that is pre-Quick Base.  I just want to build this report for 3 years of history as once we are down the road a bit more with Quick Base that is the range we want to see.  Possibly just against last year so two years could work as well but I know how to change that.

Thanks in advance for the help!

Happy Holidays and Happy New Year!



------------------------------
Ivan Weiss
------------------------------
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago

    You should sort the chart on the invoice date field grouping by month.  Then the months will be in the correct sequence.

    As for a cumulative report, you can use a waterfall chart type for that. It shows the step for each month and the steps cumulatively climb up.  So that will give you both the months visually and the cumulatively for a single year.

    Perhaps if you land the user on a Home Page with various charts you can get close to what you want all on one page.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------

    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago

      Hi Mark, I looked at the waterfall as that of course looked soooo easy except it does not compare data....  I cannot put the previous year on the same chart against it.  I need to run a comparison of two sets of data (2019 and 2020 for example)



      ------------------------------
      Ivan Weiss
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago

        I have some thoughts as to how this might be possible, but I have have to work with you on a one on one basis to try to get this to work because the solution is fuzzy in my head.  The solution would involve a table of dates Key field of the first of the month for all the possible months (loaded up using Excel).  Then it would take 24 or 36 relationships depending on how far back you wanted to go.  Contact me by email if you want to work on a one on one basis.



        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago

    Ivan,

    I suggest Tableau or PowerBI.  You are building a visualization where the parameters in it are not neatly part of the Quick Base relationships.  You need to be able to independently build the values that you want to display and independently craft the x axis values to show them.  Quick Base excels at workflow and basic reporting, you are moving into the BI/BA world and need a new tool set.

    Boston knows this and created a connector to PowerBI to assist.  Here is the link to the article on it:

    https://help.quickbase.com/user-assistance/power_bi_with_quick_base.html

    Reach out if you would like to discuss getting all your data to SQL so you can fully exploit PowerBI, Tableau or other analysis tool.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

    • AdamKeever1's avatar
      AdamKeever1
      Qrew Commander
      2 years ago

      I am getting this error:

      using this summary formula on a line chart:

      var text QUERY = "{56.EX.'XXXX'}AND{101.EX.'XXXX'}AND{278.EX.'XXXX'}AND{299.EX.'0'}AND{351.EX.'XXXX'}AND{352.EX.'XXXX'}AND{85.GTE.'1'}AND{85.LTE.'12'}";

      SumValues(GetRecords($QUERY),363)

      What am I doing wrong?

      Fields 56, 101, 278, 351, and 352 are text fields and the 'XXXX's replace specific text values for filtering. Field 299 is a checkbox field. Field 85 is a numeric field and I want records with values from 1 to 12. The sum field, 363, is a numeric field.



      ------------------------------
      Adam Keever
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        2 years ago

        Try tjhis

        SumValues(
        GetFieldValues(
        GetRecords($QUERY),        363))



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------