Forum Discussion
Daily Delta Time Intelligence
Hi
I'm trying to figure out how to calculate daily changes (delta) in a time series. At the same time I need to be able to handle missing days and setting the value to zero on those days, and on the last day I also need to set the value to zero.
Here is an example of what a want to achieve:
In my dataset I have the black figures and I want to calculate the red ones. When setting Value = 0 in the end I only want a number on the first day after the last observation i.e. I only want numbers on 09-07-2018 and not 10-07-2018, 11-07-2018 etc.
The calculation needs to be applied on a trade level basis on a datasset of hundreds of trades.
Thanks in advance.
Hi lapa44 -
Without see a sample of the data, I'm making a few assumptions. If the dates with blanks are in your data set then you can use a find and replace to change the null values to zero in the query window. If there are specific days where you need the data to be 0, then you can create a seperate date table and merge with your fact table and create a conditional column to set the specific dates to 0
In terms of the delta change.. You can download a sample file here..
https://drive.google.com/file/d/14GpDdpFTgDL5NV73vHU7P20mJxVCxHPD/view?usp=sharing
Hope this helps.
D
3 Replies
Hi lapa44 -
Without see a sample of the data, I'm making a few assumptions. If the dates with blanks are in your data set then you can use a find and replace to change the null values to zero in the query window. If there are specific days where you need the data to be 0, then you can create a seperate date table and merge with your fact table and create a conditional column to set the specific dates to 0
In terms of the delta change.. You can download a sample file here..
https://drive.google.com/file/d/14GpDdpFTgDL5NV73vHU7P20mJxVCxHPD/view?usp=sharing
Hope this helps.
D
How are you, brother davehus
I saw your solution to the problem and I liked it, but I had a problem with the my data, which is that each date is repeated three times because of the column of Status I hope you help me plz
Country Last_Update Incident_Rate Status Cases_Number Previous Last_Update
Spain 04/16/2020 395.570224334054 Recovered 04/15/2020 Spain 04/16/2020 395.570224334054 Active 184948 04/15/2020 Spain 04/16/2020 395.570224334054 Delta_Confirmed 7304 04/15/2020 Spain 04/16/2020 395.570224334054 Deaths 19315 04/15/2020 Spain 04/15/2020 379.948293204569 Recovered 04/14/2020 Spain 04/15/2020 379.948293204569 Active 177644 04/14/2020 Spain 04/15/2020 379.948293204569 Delta_Confirmed 5103 04/14/2020 Spain 04/15/2020 379.948293204569 Deaths 18708 04/14/2020
Hi lapa44,
The solution of davehu should close your case.
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
