Last week data

EChampagne

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!

Sean_Tully

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.

GrantSmith

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:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

Sean_Tully

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.

GrantSmith

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:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons