calculating variance with mysql week over week

user095063

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.

GrantSmith

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.

jaeW_at_Onyx

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.

 

user095063

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)
)

user095063

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

jaeW_at_Onyx

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

 

 

user095063

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

jaeW_at_Onyx

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!