Previous day values - Following Day Values Restart Calculation Each Month
Hey guys,
I have a dataset that appends each day to include the daily downloads, and resets the calculation on the 1st of every month.
This issue is, it's cumulative totals, so if yesterday we had 40 downloads and the following day we had 15 downloads, the total for the following day would come in as 55 total downloads.
What I want to do is capture the difference so I could get daily downloads.
I was using the Date Format widget in Magic ETL, but realized that it's not as simple because if the data resets each month, the last day of the month is a final cumulative sum of all downloads. So If the 31st had 24567 downloads and the 1st says 12, 24567-12 is not accurate.
I've included some sample data so you can see what I mean. I may be overthinking this.
Answers
-
Since you mentioned Magic ETL you can utilize a Rank and Window function to calculate the LAG for the download field. You can then take the current download count for that day and subtract the lag value to get the difference increase for your day. You'll want to make sure to partition based on the year and month of your date since the count resets each month.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
oof... don't do it! :P I wouldn't code this into ETL.
use the LAG function in analyzer: https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=1s
UNLESS you have gaps in your data. in which case you might want to consider the route of creating the 'universe of possible values' as outtlined here: https://www.youtube.com/watch?v=Xb4QgKYgaqg&t=353s
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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive