Calculating the cumulative sum of a value for a given time window

user04174
user04174
in Archive

I am trying to calculate the cummulative monthly sum of a specific value but with the caveat that said value only exists for a particular time window. There will be an associated startDate and an associated endDate for each value. An example data table detailing an example data set is given here:

 

Screen Shot 2018-06-27 at 14.22.43.png

 

A table detailing the resulting calculated cummulative sum is given here:

 

Screen Shot 2018-06-27 at 14.18.50.png

 

And the resulting plot I would be looking for is given here:

 

Screen Shot 2018-06-27 at 14.22.51.png

 

Having reproduced this propblem in Excel. (N.B. that I have used quarters as an example, but I would need the granularity to be monthly).

 

May I please have advice on how best to accomplish this within Domo. I don't believe that this should be hard but I am really struggling. Would you recomend using the MySQL Magic ETL to create a new data series or can I do this within the analyser window by making use of filters (for example)?

 

Thank you in advance.

 

Regards,

Matthew

Comments

  • laura_igl
    laura_igl Member

    Hi,

     

    Is anyone able to help with this request?

     

    Thanks,

  • Jarvis
    Jarvis Domo Employee

    Hello!

    You can create this referenced chart by making a beast mode calculation that contains the following:

     

    CASE

    WHEN `startDate` LIKE '%1Q17%'

    THEN 'Quarter 1'

    WHEN `startDate` LIKE '%2Q17%'

    THEN 'Quarter 2'

    *(...etc)

    ELSE 'Quarter 0'

    END

     

    You can then have your 'value' as the Y Axis, with a SUM to represent the total values for each quarter or date range.

Categories