Previous day values - Following Day Values Restart Calculation Each Month

leeloo_dallas

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.

sample data.xlsx

GrantSmith

Hi @leeloo_dallas

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.

jaeW_at_Onyx

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