Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

Diff function for date does not work

Solved!
llyly32
Level 2
Diff function for date does not work

Hi, I'm usind the Diff function to calculate a difference between two dates and I want the formula to return me the difference in weeks. 

This formula works well for almost all the cases but for the case when there is only 1 week between two dates (for example 2021/41 and 2021/42) the function returns me 0 instead of 1. 

I don't know how to face this issue. Can you help me ? Maybe it's a problem of parameters ? 

 

Thank's

0 Kudos
1 Solution
Marine
Dataiker
Dataiker

Hi @llyly32 ,

Thanks for these precisions, now I get it!

The 'diff" function returns only whole week count, namely 7 days. It does not count incomplete weeks with less than 7 days. To achieve your expected outcome, you should make sure that the days are at the start of the week. The "trunc" function, which rounds a date to the first day of the specified time unit, can help you compute what you want :

diff(trunc('2021-10-25', "weeks"), trunc('2021-10-21',"weeks"), 'weeks')

 

View solution in original post

0 Kudos
4 Replies
Marine
Dataiker
Dataiker

Hi @llyly32  , 

Thanks for reporting this issue. What is your exact formula? 

The following formula returns 1 for me : 

diff('2021-10-18','2021-10-11', 'weeks')

 

0 Kudos
llyly32
Level 2
Author

Hi @Marine 

Yes it's this function. When I used it with these dates : 

diff('2021-10-25', '2021-10-21', 'weeks')

These two dates are in two weeks differents (2021/43 and 2021/42) but the function returns 0 instead of 1. 

I think the problem is because there is less than 7 days between 21/10 and 25/10 but I'm not sure. 

Do you understand what I mean ?

0 Kudos
Marine
Dataiker
Dataiker

Hi @llyly32 ,

Thanks for these precisions, now I get it!

The 'diff" function returns only whole week count, namely 7 days. It does not count incomplete weeks with less than 7 days. To achieve your expected outcome, you should make sure that the days are at the start of the week. The "trunc" function, which rounds a date to the first day of the specified time unit, can help you compute what you want :

diff(trunc('2021-10-25', "weeks"), trunc('2021-10-21',"weeks"), 'weeks')

 

0 Kudos
llyly32
Level 2
Author

@Marine thank you for your solution. It's work fine ! 🙂