Last week data
How do I sum a measure (activities) specifically for last week (Monday - Sunday) within ETL? I also would want to do for the week before.
For context, my ultimate goal is to take an existing formula in ETL showing total potential activities for the week (which is constant) and then subtract the actual activities for the week to see how much capacity was/wasn't available.
Thanks in advance!
Best Answers
-
I would do this by using a formula tab to get the week number for each row, WEEK(`Date Column`,22). The '22' in this formula makes Monday the start of the week.
Then I would do a couple of filters in separate streams from the data - WEEK(`Date Column`,22) = WEEK(Current_date(),22) -1 and WEEK(`Date Column`,22) = WEEK(Current_date(),22) -2. This would give you last week and two weeks ago. A group by off of each of these tiles would give you the sums for those weeks. Then, you'd have to join them back to the original data. You could use join formulas similar to the filters - WEEK(`Original_data.Date Column`,22)-1 = WEEK(`One_week_ago.Date Column`,22) and WEEK(`Original_data.Date Column`,22)-2 = WEEK(`Two_weeks_ago.Date Column`,22). That would bring the measures onto the same rows so you can do the comparison you want to do.
There may be more steps, or a different join, depending on the actual structure of your data, but hopefully this points you in the right direction.
0 -
When I'm doing period over period (WoW in your case) analysis I like to restrucutre my data so that each day has a relative entry in the dataset. This will allow you to go back in time and see what the capacity and activity levels were no matter the date. I've done a write up in the past on how to do this here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
I would do this by using a formula tab to get the week number for each row, WEEK(`Date Column`,22). The '22' in this formula makes Monday the start of the week.
Then I would do a couple of filters in separate streams from the data - WEEK(`Date Column`,22) = WEEK(Current_date(),22) -1 and WEEK(`Date Column`,22) = WEEK(Current_date(),22) -2. This would give you last week and two weeks ago. A group by off of each of these tiles would give you the sums for those weeks. Then, you'd have to join them back to the original data. You could use join formulas similar to the filters - WEEK(`Original_data.Date Column`,22)-1 = WEEK(`One_week_ago.Date Column`,22) and WEEK(`Original_data.Date Column`,22)-2 = WEEK(`Two_weeks_ago.Date Column`,22). That would bring the measures onto the same rows so you can do the comparison you want to do.
There may be more steps, or a different join, depending on the actual structure of your data, but hopefully this points you in the right direction.
0 -
When I'm doing period over period (WoW in your case) analysis I like to restrucutre my data so that each day has a relative entry in the dataset. This will allow you to go back in time and see what the capacity and activity levels were no matter the date. I've done a write up in the past on how to do this here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive