Omni’s native Period-over-Period comparison is a powerful way to analyze trends between related time frames, such as comparing performance across weeks, months, or years. But what if you need to compare two periods that don’t have a logical sequential relationship? For example, an eCommerce or retail company might want to compare sales performance during Black Friday week versus Memorial Day week.
To enable these kinds of comparisons, we can leverage filter-only fields, templated filters and filtered measures.
First, we’ll need to create two filter-only fields that will allow users to select the two periods they want to compare.
filters:
current_period:
type: timestamp
previous_period:
type: timestamp
Then, using templated filters we pass the selected periods to our date field. *Note if you have more than one schema in your database connection, views like users will need to be scoped with schema name like “schema__users”.
previous_period_flag:
sql: |
{{# users.previous_period.filter }} ${users.created_at} {{/users.previous_period.filter }}
current_period_flag:
sql: |
{{# users.current_period.filter }} ${users.created_at} {{/ users.current_period.filter }}
Finally, we can create filtered measures that filter on the dimensions we created above.
count_previous:
aggregate_type: count
filters:
previous_period_flag:
is: true
count_current:
aggregate_type: count
filters:
current_period_flag:
is: true
With this setup, users can seamlessly compare arbitrary timeframes directly from their workbook or dashboard, and the filtered measures will update dynamically to reflect their selections.
#templatedfilters