With great power comes great responsibility
Creating dynamic dashboards that adapt to different needs can provide tremendous flexibility. By incorporating features like a timeframe selector, field control, or templated filter, you can streamline the information displayed, making it easier to manage and interpret. However, as you introduce these powerful capabilities, you may encounter scenarios where certain combinations of dimensions, timeframes, and measures should not be visualized. Let’s explore how to maintain clarity while applying necessary constraints to your data visualizations.
However, this additional functionality may result in combinations of dimensions, timeframes, and measures that we do not want to visualize. How can we implement guardrails without sacrificing this powerful clarity?
Let’s consider an example centered around financial reporting. Suppose we have a financial table we want to report at both a quarterly and yearly level. We can utilize a timeframe selector to toggle between these options.
We’ll report three measures: Sale Price Sum, Sale Price QoQ%, and Sale Price YoY%. As shown in the screenshot below, all measures function as expected at the quarterly level.
We define Sale Price QoQ% as follows, using a flattened pivot from Period over Period analysis and Snowflake’s div0 function:
div0((${order_items.sale_price_sum} - ${order_items.sale_price_sum_previous_quarter}), ${order_items.sale_price_sum_previous_quarter})
Similarly, Sale Price YoY% is defined as:
div0(${order_items.sale_price_sum} - ${order_items.sale_price_sum_previous_year}, ${order_items.sale_price_sum_previous_year})
Behavior We Don’t Want
When we switch the date aggregation to a Year using the timeframe selector, the QoQ% measure is calculated in a way that does not align intuitively with our reporting preferences for yearly aggregations. In contrast, YoY% makes sense at both quarterly and yearly levels.
Editing Measures to Be Blank for Certain Dimensions in the Query
To address this, we’ll update the QoQ% measure to depend on the presence of the Created at Quarter dimension in the query by using Omni’s in_query. This can be achieved with mustache syntax {{ view_name.field.in_query }} in the measure’s definition.
The revised definition for Sale Price QoQ% will be:
case
when {{ order_items.created_at[quarter].in_query }} then
div0((${order_items.sale_price_sum} - ${order_items.sale_price_sum_previous_quarter}), ${order_items.sale_price_sum_previous_quarter})
else
' '
end
This change ensures that the measure is blank for yearly aggregations and populated for quarterly aggregations, as shown in the screenshot below.
Conclusion
In conclusion, by utilizing the in_query function, you can tailor your measures to show appropriate fields/value or even remain blank depending on the level of aggregation or fields in the query. This approach ensures that your dashboards remain intuitive and aligned with your reporting preferences across different timeframes.