Quick question I hope you can help with.
If I have a date column with a list of dates in, what formula do I need to use to work out the number of days between the dates?
the number of days between these two dates would be: 5 days for the first one and then 4 days for the second.
I cant just use diff days because that needs to have the dates in separate columns. Maybe I need to create a Date-1 column or something and then use that column to do diff_days?
The following is a potential solution. I would be interested in understanding other alternatives.
1. Load the Date column as an Integer value. I could not get to_integer to work on date.
Date Number 20/12/19 43819 25/12/19 43824 29/12/19 43828
2. Create formulas to calculate the difference using Moving_Sum
Current = sum(number) or moving_sum (number , 0,0,date )
Previous = moving_sum (number , 1,-1 ,date )
Diff = current - previous
You can achieve this without having to load the number of the date into the database by using the diff_days function, in which case, the formula would be:
Current = sum(diff_days(date,today())) or moving_sum (diff_days(date,today()) , 0,0,date )
Previous = moving_sum (diff_days(date,today()) , 1,-1 ,date )
I've used today as the reference point, but you can use any arbitrary date as the reference point.