Best Mode: Cumulative Sum

user025699

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

GrantSmith

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.

GrantSmith

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.

user025699

Thanks, @GrantSmith! I will try the second option.. I hope it works!