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.
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
0
Categories
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 337 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 83 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有