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

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

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

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 ! ๐Ÿ™‚