time difference across dates in a column
Hello Team,
I have a use case where I would like to the storage size in the following scenario
you will notice the date increments by 1 day all the way until Dec 27, where the storage sizes only return after a few days, then a month.
my use case is get to a position of below
-
Hello Anson.
As far as I understand, you would like to add a new column that will represent the difference in days between the previous and the current creation_date_string value, wouldn't you?There is the TIMESTAMPDIFF function that returns the number of whole date intervals between two date arguments. It could be used to calculate the difference in days between two dates.
However before applying this function you have to prepare your dataset. I would go with the following approach.
- Ensure that the initial data is sorted by creation_date_string column.
- At the initial Sheet, create a new column (let's call it Identifier) and add the same value to each record. You could just use any integer or a string.
- At the new Sheet, apply GROUPBY(#InitialData!Identifier) to get all records in a single group.
- Then add GROUP_PREVIOUS(#InitialData!creation_date_string). This will return previous creation_date_string value for each record.
- Then reference #InitialData!creation_date_string and the next column.
- Add MIDNIGHT function to both creation_date_string and previous_creation_date_string columns to round the timestamps up to midnight. As far as I understand, you only care about the day when the new data has been generated not the exact time it happened, don't you?
- Introduce TIMESTAMPDIFF("DAY";#previous_creation_date_string;#creation_date_string) to calculate the difference between the new data portions in days.
- Reference any other columns you need from the InitialData.
I hope this helps. Do not hesitate to let me know if you have any further questions.
Please sign in to leave a comment.
Comments
3 comments