Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Diff_months - how does it work?

  • 30 July 2019
  • 3 replies
  • 1569 views
Dawid
Userlevel 7
Badge +1

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?

Dawid

3 replies

maxcorbin
Rank
Userlevel 3
Badge

Hey @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 diff_months function returns 2 as expected.


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.diff function from the moment library for JavaScript. I went looking in their docs and found this explanation of the way it is implemented in 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:


// Jan 31 - Feb 28

31 - 28 = 3;          // difference in days

(31 + 28) / 2 = 29.5; // average month length

3 / 29.5 = 0.101;     // the partial difference in months

0 - 1 = -1;           // the integer difference in months

-1 + 0.101 = -0.899;  // the total difference in months

So they:



  1. calculate the difference in days (extract_days on the start and end date and simple subtraction)

  2. Find the average month length (find the total number of days in a month, so for May it would be 31 since 05/31 is the last day of that month)

  3. Divide the difference in days from Step 1 by the average month length from Step 2

  4. Find the simple difference in months (extract_months on the start and end date and simple subtraction)

  5. Add the results of Step 3 and Step 4 together to find the total difference.


Looker then truncates that value to an integer, so -0.899 here becomes 0.


Let me know if you have any more questions about how this works, or if I’ve missed anything in my explanation!

Dawid
Userlevel 7
Badge +1

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

Dawid
B

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:


    ((extract_years(date_B) - extract_years(date_A))*12) + (extract_months(date_B) - extract_months(date_A))

Hope this helps!

Reply


Terms of Service