Discussions

 View Only
Expand all | Collapse all

Cumulative Sales Line Chart

  • 1.  Cumulative Sales Line Chart

    Posted 12-28-2020 15:10
      |   view attached

    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
    ------------------------------


  • 2.  RE: Cumulative Sales Line Chart

    Posted 12-28-2020 15:21

    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
    ------------------------------



  • 3.  RE: Cumulative Sales Line Chart

    Posted 12-28-2020 15:24

    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
    ------------------------------



  • 4.  RE: Cumulative Sales Line Chart

    Posted 12-28-2020 15:43

    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
    ------------------------------



  • 5.  RE: Cumulative Sales Line Chart

    Posted 12-29-2020 11:21

    Thanks Mark, let me see if I sorted it out first.  I think I might actually have it leveraging what you showed me with the sales goals when we did that....

    I created a field for the year based on the date of the invoice and made a Cumulative Sales Table.  I created that relationship using the year as the reference field so it is auto populating that relationship now.  That successfully has given me annual cumulative sales.

    I created an import with an automation to grab those values on the last day of the month and copy the record to another table.  It also populates the date of the import run (last day of the month).  I believe, if my logic is correct, that means every month I am going to have a record on that table with the cumulative sales to date.  I should be able to create a line chart based on that.

    It will run for the first time on Dec 31st so we'll see how that works out.



    ------------------------------
    Ivan Weiss
    ------------------------------



  • 6.  RE: Cumulative Sales Line Chart

    Posted 12-29-2020 11:27

    That is an interesting  approach. 

    Sounds like that could work on a going forward basis but will not deal with your historical data if for example you're looking to track last year to date for the same month versus this year to date for the same months.

    But you could always populate the last year to date values manually to get started.



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



  • 7.  RE: Cumulative Sales Line Chart

    Posted 12-29-2020 11:58

    Exactly, I am going to wait to see after it runs this first automation if it puts in the data I believe it should.  If it works I manually back fill it this one time and moving forward should do the trick.  I just need to populate a new record each year moving forward for the upcoming year which is certainly easy enough to do.



    ------------------------------
    Ivan Weiss
    ------------------------------



  • 8.  RE: Cumulative Sales Line Chart

    Posted 12-29-2020 09:13
    Edited by Don Larson 12-29-2020 09:13

    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
    ------------------------------



  • 9.  RE: Cumulative Sales Line Chart

    Posted 02-07-2022 14:41

    This is an older (pre formula query post), but for those coming across this thread, this is how you can achieve this today...

    See this post for references in the video: https://www.quickbasejunkie.com/blog/how-to-cumulative-charts



    ------------------------------
    Quick Base Junkie
    ------------------------------



  • 10.  RE: Cumulative Sales Line Chart

    Posted 04-28-2023 15:55

    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
    ------------------------------



  • 11.  RE: Cumulative Sales Line Chart

    Posted 04-28-2023 15:58

    Try tjhis

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



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



  • 12.  RE: Cumulative Sales Line Chart

    Posted 04-28-2023 16:01



    ------------------------------
    Adam Keever
    ------------------------------



  • 13.  RE: Cumulative Sales Line Chart

    Posted 04-28-2023 16:15

    I was using a summary formula when I should have been using a report formula...



    ------------------------------
    Adam Keever
    ------------------------------



  • 14.  RE: Cumulative Sales Line Chart

    Posted 04-28-2023 16:23

    well that was unexpected...





    ------------------------------
    Adam Keever
    ------------------------------