How can i subtract current values from previous week values?

gbrown
gbrown Member
in Magic ETL

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?

image.png

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

Best Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓

    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.

    image.png
    image.png

    Which yields the following

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓

    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.

    image.png
    image.png

    Which yields the following

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • gbrown
    gbrown Member

    Thanks. Is this how I would set up the lag function? @david_cunningham

    image.png
  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories