calculating variance with mysql week over week

I need to display week over week difference with mysql in `Week Over Week Users` column. My data looks like the following:
```
Date                 Users          Week Over Week Users
06-01-2019      10                 10
06-08-2019      15                 15
06-15-2019       5                   5
```
Currently, `Week Over Week Users` only reflects the data that I have in `Users` column. The desired output would be:
```
Date                  Users                Week Over Week Users
06-01-2019         10                          10
06-08-2019         15                           5
06-15-2019          5                           -10
```
Basically if on the second week the number of users grew up to 15 users, then I need to display 5 (as in +5 users since last week, so `new week Users - last week Users` would be the formula)

Here is my code:
```
(
SUM(
CASE
WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) THEN `Users`
ELSE 0
END
) - SUM(
CASE
WHEN WEEK(`Date`) = WEEK(CURRENT_DATE()) - 1 THEN `Users`
ELSE 0
END
)
)
```
But it doesn't work as it duplicates the Users column.

Comments

  • Hi @user095063 

     

    It sounds like your use case is perfect for the LAG windowing function withing a beast mode (you may need to talk to your CSM to get this feature switched on).

     

    @jaeW_at_Onyx has a really nice video outlining this here: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Beast-Mode-Sliding-Window-Function-with-Partition/m-p/48413/highlight/true#M8268. He's doing it month over month but the beast modes could be tweaked to get each week.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Thanks for sharing my solution @GrantSmith , it should work for your use case @user095063 .

     

    An alternative you could also consider building an offset into your data by using an offset table with a Fusion.

    https://www.youtube.com/watch?v=CDKNOmKClms&t=705s

     

    In this example the offset is one year, but you could easily modify to one week or one month.

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thank you @jaeW_at_Onyx  for the response, this should have worked, but not displaying anything for some reason.

     

    (
    sum( case when week(`Update Date`) = week(current_date()) then `Users` end) -
    sum( case when week(`Update Date`) = week(current_date()) -1 then `Users` end)
    )

  • @jaeW_at_Onyx 

    I have also tried the following:

     

    sum (

    CASE when week(`Update Date`) = WEEK(CURRENT_DATE()) -1 then `Users` else 0
    end ) - 
    sum (
    CASE when week(`Update Date`) = WEEK(CURRENT_DATE()) then `Users` else 0
    end) 

     

    It shows the right result for the last week, but for the current week, it shows the sum of users for the current week with a minus. Not the difference between last week and the current week. 

     

    I am really banging my head against the wall here, also LAG is not available for me, but it seems like something simple as the difference should not require much, yet it doesn't work at all...

  • Make sure your Beast modes for 'Users this week' and 'Users last week' return appropriate values when separated into two separate BMs.

     

    Then, make sure you're not Getting NULL b/c you can't add x + NULL (you get NULL).

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • I did check the users values for both weeks separately, and it does return the right values. It is also a sample of the data, so it is very small and doesn't have any NULLs in it. I reached out to domo tech support, they can't figure that out either. Not yet anyway. I am following the docs and your videos precisely. 

    But is still doesn't work... ?

  • upload or email me a sample of your dataset pls jae@onyxreporting.com.  please make sure it's anonymized in case i decide to make a youtube video of it!

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.