Calculating the cumulative sum of a value for a given time window
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:
A table detailing the resulting calculated cummulative sum is given here:
And the resulting plot I would be looking for is given here:
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
-
Hi,
Is anyone able to help with this request?
Thanks,
0 -
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.
0
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 526 Datasets
- 2.1K Magic ETL
- 3K Visualize
- 2.2K Charting
- 441 Beast Mode
- 23 Variables
- 514 Automate
- 115 Apps
- 391 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 67 Manage
- 67 Governance & Security
- 1 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive