Daniel, it’s precisely for variance/growth calculation reasons that I decided that writing in LookML was the more appropriate approach for date calculations. Variance and growth rates are almost certain to come up in conversations with business users, so I like having them always accessible. I also have seen people use incorrect formulas for growth, so feel that that room for error can be easily eliminated (and therefore, it should).
Here’s my process:
- Implement the Date Comparison Block extend, and apply a view_label to each of the visible fields to keep them organized. I used ‘Timeline Comparison Fields’. A bonus - I can’t think of a better way to demonstrate the huge value of extends. Date Comparison Block
- On your Explore base, add individual Measures for two comparison periods, with filters applied so that they’re picking up values from the appropriate time buckets (referencing the ‘This Period’/‘Prior Period’ results). This will be measures like ‘Sales, Period 1’, or ‘Profits, Period 2’. I then put a view_label on those fields to show them within the same view as my Timeline Comparison Fields.
- Also on your Explore base, add Comparative fields for Variance and Growth and place them within your Timeline Comparison Fields.
At the end of all this fun, you get a robust date comparison extend that can be tacked onto any view with a date field with relative ease, and you’ll have something like the below: