Calculating a cumulative total in a sumo card
Hi all
I'm posting in ETL/Dataflow as I assume this is where the ultimate answer lies.
My end objective is I want to be able to present a simple table outlining my different customer cohorts (cohort is identified based on the month they placed their first order), and their average Gross Profit to us each month since joining.
Currently I can do this month by month, but I want to show this as a cumulative total too (ie per below, cumulative total for Period 2 is Period 1 plus Period 2 gross profit, then Period 3's cumulative total is period 1 plus period 2 plus period 3).
Ideally this is how I'd like it to look:
Period | Cohort | |||
March 2018 | April 2018 | |||
Gross Profit | Cumulative Total | Gross Profit | Cumulative Total | |
1 | 56 | 56 | 60 | 60 |
2 | 10 | 66 | 15 | 75 |
3 | 5 | 71 | 5 | 80 |
4 | 3 | 74 | 3 | 83 |
5 | 2 | 76 | 3 | 86 |
6 | 2 | 78 | 3 | 89 |
7 | 2 | 80 | 2 | 91 |
8 | 2 | 82 | 2 | 93 |
TOTAL | 82 | 93 |
So far I have done a rank and window using magic ETL on gross profit each month by customer (and then a grouping on customer ID and month of order), however this falls down because not every customer orders each month, hence my cumulative total increases and decreases each month based on number of people in the cohort ordering, rather than being a constant increase as I would expect.
Any help our thoughts would be appreciated.
Thanks
Kieran
Best Answer
-
For a running total like that you will need to use an ETL to manually filter the data to the running total time frame, calculate the total and then join that data back to the original.
Its a pretty manual process so if you have a lot of data it can take a while.
1
Answers
-
For a running total like that you will need to use an ETL to manually filter the data to the running total time frame, calculate the total and then join that data back to the original.
Its a pretty manual process so if you have a lot of data it can take a while.
1 -
Thanks BrendanH.
I ended up doing a SUM group by (on amount charged, grouping by the cohort group and month & year of order) in my magic ETL after doing the rank & window.
It means I've lost the transactional level detail if I want to drill further into the data, but it's given me good aggregated data to show each cohort's spending by month as well as aggregated over their lifetime buying from us.
Thanks for the help!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive