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!**0 -
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"0 -
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)
)0 -
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...
0 -
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"0 -
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... ?
0 -
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"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive