Best Mode: Cumulative Sum
Hi All,
I am trying to create in Best Mode a Cumulative Sum of column `Amount` to be represented in a table format.
I want to take the `Amount` 50 and count it for all months from `Start Date` to `End Date`. In the example below, I would like to count 50 for July, August, September, October and November.
Do you know if that is possible in Best Mode? I have tried a few formulas, but I can't get the results I am looking for.
ITEM | Amount | Start Date | End Date |
A | 50 | 2020-07-16 05:00:00 | 2020-11-12 05:59:59 |
B | 10 | 2020-01-01 06:00:00 | 2020-04-18 04:59:59 |
Thanks!!!
Best Answer
-
Hi @user025699
Short Answer: You need to reformat your data so you have one record per month instead of a start + end record.
Beast modes can't create new records (which is what you'd need to do if you want to count 50 for each of those months). You'd need to restructure your data with a dataflow and break out the monthly counts so that you have a record designating 50 for each month in between your start and end dates.
Alternatively and perhaps the better option is to have a date reference table where you can have a table full of dates (Domo has a dataset from the DomoDimensions connector called Calendar Dates) and then join to that table based on each date being between your start and end date, group it based on the item, year and month and take the minimum (or maximum since all the values should be the same) of the amount to get the monthly amount. The one caveat to this is that if you're grouping by year and month in a data flow or data view then you won't be able to get any more granular than a monthly time slice.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Hi @user025699
Short Answer: You need to reformat your data so you have one record per month instead of a start + end record.
Beast modes can't create new records (which is what you'd need to do if you want to count 50 for each of those months). You'd need to restructure your data with a dataflow and break out the monthly counts so that you have a record designating 50 for each month in between your start and end dates.
Alternatively and perhaps the better option is to have a date reference table where you can have a table full of dates (Domo has a dataset from the DomoDimensions connector called Calendar Dates) and then join to that table based on each date being between your start and end date, group it based on the item, year and month and take the minimum (or maximum since all the values should be the same) of the amount to get the monthly amount. The one caveat to this is that if you're grouping by year and month in a data flow or data view then you won't be able to get any more granular than a monthly time slice.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks, @GrantSmith! I will try the second option.. I hope it works!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 603 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive