Time Intelligence & Comparison: best practice for code modularity

[enrico_spada-16]

Hi,

what is the best practice in terms of code modularity for implementing time intelligence & comparison such as:

• Month-To-Date: value of a given KPI from the beginning of current month to today
• Month-To-Date Previous Year: previous year value of a given KPI from the beginning of current month to today
• Month-To-Date vs. PY: [Month-To-Date] - [Month-To-Date Previous Year]

Since the formulas for this KPIs are standard, ideally they are defined in one single place and then referred when needed to apply such time intelligence / comparison.

For example, in Power BI the standard is to use Calculation Groups. What is the Looker equivalent of this approach?

Thank you

[pankajrb]

I wouldn't consider today whenever there is comparison with the previous period as it is incomplete data.

[salehaxsid]

There is no looker equivalent to calculation groups, you can either try filters within looks that works in MTD and YTD fashion or create another explore of existing view that has sql_where condition that includes YTD or MTD logic and join it with original explore.

[andy4]

When you apply a "Past 1 month" filter, that will grab all the days from the current month. "Past 1 complete months" will only show days from the most recently completed month.

A "12 months ago for 1 month" filter will get a month from last year.  This won't work well if you want to compare MTD (i.e. this month, an incomplete month) to a month from last year. You would need to do something like "past 30 days" to "365 days ago for 30 days." If you really want to compare MTD to the same number of days from this month last year, something like a "day of month" filter would be needed. I'm not sure whether you can use the advanced filter options to do that, but a LookML field might look like this:
dimension: mtd_last_year {
type: yesno
sql: ${datefield_day_of_month} <= EXTRACT(day FROM current_timestamp()) /* add a -1 here to account for "complete days" */
AND ${datefield_year} = (EXTRACT(year FROM current_timestamp()) - 1) ;;
}

You can use the gear icon on your measure to "Create a filtered measure." This will let you apply a date filter to only one result column. This is better, in the case of period over period comparisons, than using the filters pane since filters in the filters pane apply to the entire query - you just want them to apply to one measure each so you can compare them.