How can i subtract current values from previous week values?

gbrown · May 22

Hello,

I am trying to subtract current week values with previous week values and have the ability for this to update each week. How would I do this in Magic ETL? A lag function?

So 5-19 week would subtract from 5-12 week and then next week I would like to see 5-26 week subtract from 5-19.

Thanks,

Garrett

david_cunningham · May 22

Assuming your data will always be grouped by week, you could use a lag function. You would use a Rank & Window tile to achieve this in ETL. One thing to keep in mind, is that lag will always go to the last value, so if you are missing data for a week, it would go back 2 weeks. If you want to avoid this, you will need to fill in your missing weeks by joining up a date dimension table.

Here is an example of how you could set it up.

Which yields the following

david_cunningham · May 22

@gbrown it looks like you have this set up to lag 300 rows. Is that what you want?

You can always test the output by running a preview in ETL, and then checking the output to see if it matches what your desired output is.

GrantSmith · May 23

I'd recommend restructuring your data with a custom date offset so you can easily calculate the prior week. I've done a write up on it here:

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

david_cunningham · May 22

Assuming your data will always be grouped by week, you could use a lag function. You would use a Rank & Window tile to achieve this in ETL. One thing to keep in mind, is that lag will always go to the last value, so if you are missing data for a week, it would go back 2 weeks. If you want to avoid this, you will need to fill in your missing weeks by joining up a date dimension table.

Here is an example of how you could set it up.