Does it work on a month as 30-day period or calendar month… it returns 1 if I compare today 30/07 with 31/05, shouldn’t that be two?
Already have an account? Login
Login to the community
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
@Dawid_Nawrot! This is a good question. I think that the behavior you’re seeing makes sense if you are thinking the difference between two days in “full” months. Since 05/31 is the last day of the month, then a “full” month has only passed if the end date is the last day of the month. The last day of July is 07/31, making 07/30 the second-to-last day of the month, so only one “full” month passes between 05/31 and 07/30. If you use 05/31-07/31, then Looker’s
However, I wanted to make sure, so I did a bit of digging and one of our engineers told me that we use the
Moment.difffunction from the
moment. This pull request lays out their thinking behind their decision: https://github.com/moment/moment/pull/571
This example shows how their approach can sometimes yield unintuitive results:
extract_dayson the start and end date and simple subtraction)
extract_monthson the start and end date and simple subtraction)
Looker then truncates that value to an integer, so
Let me know if you have any more questions about how this works, or if I’ve missed anything in my explanation!
Okay I know Moment a little bit and see why they think that. The only reason why I might disagree a little bit is that (assumption) most of us, who use BI Tools, come from some kind of SQL background and most often than not, we expect the behaviour of such functions to be as close as to those of SQL (I know SQLs might differ too).
I do have JS and PHP background too, so it’s not like it’s a rocket science to me, but somehow I wish a lot of coding behind Looker resembled more of what usually happens in SQL.
Anyway, thank you for detailed explanation
I found a decent workaround. You can sort of re-construct the diff_months () function using extract_months and it will calculate purely on the number values of the months. You’ll need to include extract_years () if you’re also looking at multiple years. But this custom dimension gave me the results I was looking for:
Hope this helps!