Hi everyone, I have a row with my product id (on the left) and its takeoff time. As you can see all takeoff times are in the same row so I cant use a “normal” date_diff. I tried to rank them or bring the previous takeoff time (denserank function or lag) but again reach the same problem while trying to use datediff as it cant work with aggregated and not aggregated fields. Any ideas? at the end of the day I wan to know how many days past between the latest takeoff to the one before that…
Thanks!
1 Like
Hello @Etai, you are really close to the solution! You can use dateDiff here, the problem is you need to aggregate your takeoff_time field. Since you already have your data partitioned, you can use min() and it will return the same value while ignoring the error message. It would look like this:
ifelse(isNotNull({Previous takeoff}), dateDiff({Previous takeoff}, min({takeoff_time}), 'HH'), NULL)
I used hours as the date type for the datediff function. Change that to the time value you need and this should resolve the error you are facing. I’ll mark my response as the solution but let me know if you have any remaining questions. Thank you!
Thanks very much for the answer. I was able to do it 
1 Like