Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Cumulative Line Chart

Hello,

I am building the CRM aspects of my application and I am looking to do several charts that compare this year compared to last year by date.  So for example I will have a line graph with two running lines by month (Jan, Feb, Mar, etc.)  They are cumulative sales year to date compared to last year to date.  Same for opportunities, etc.  How many we have won to date vs last year.

I am not quite sure how to get this represented.  I saw some posts when searching about a waterfall chart.  But I dont think I can compare to last year that way...

Attached is a sample of what I am trying to replicate.  I manually do this in powerpoint charts from data coming out of my existing CRM
reports and charts
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago

    I think that this can be done, but as there is not a native waterfall chart style to support a "series" and the waterfall is the only chart type which does Cumulative charts, you will need to use a line chart and have the series be by Year and calculate the cumulative values within a new table in the app.  

    There will ned to be a Parent table (called perhaps Monthly Sales) loaded up with a bunch of text fields (from a one time Excel upload) for say the next 20 years the format YYYYMM 

    201801
    201802
    201803
    etc


    You will need to have a field for the Year so you can do the series on the chart, so calculate that by taking the Left 4 characters of the YYYYMM field.  

    You will also need a field for the MM part to make that one as well. 

    Set the YYYYMM field to be to Key field of Monthly Sales.

    Make a relationship to get your sales data up to these records.  To do that you will need to build a formula to calculate the the YYYYMM text field and use a summary field on the relationship to get to total sales for that month, for example call it [Current Month Sales].

    Ah, but you ask what about the cumulative issue?  

    To do that make a relationship of the Parent table to itself.  It will create a field called [Related Monthly Sale] and that is OK, but then edit that field to name it [YYYYMM Last Month] and make it a formula text field.  Create a formula to calculate the last month, so for example on the record for 

    201802, the value would calculate to 201801.    But for 201801 it would have to calculate to null.

    Almost last, you can make a numeric formula field on the record called [YTD Sales], but leave the formula blank. 

    The look that field up in the relationship and call it [YTD sales to Last Month].

    Then you should be able to go back to the [YTD Sales]  formula and make it be 
     
    [YTD sales to Last Month] + [Current Month Sales]

    Now, finally you would be able to make your line chart based on the [YTD Sales] field and set the series to be year and the X axis to be MM