# How to Create a Measure That is Blank for Certain Levels of Aggregation

**URL:** <https://community.omni.co/t/how-to-create-a-measure-that-is-blank-for-certain-levels-of-aggregation/194>\
**Category:** Patterns\
**Tags:** calcs, modeling\
**Created:** [December 3, 2024, 10:59am UTC](https://community.omni.co/t/how-to-create-a-measure-that-is-blank-for-certain-levels-of-aggregation/194 "2024-12-03T10:59:08Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conor](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conor/32/45_2.png) [@conor](https://community.omni.co/u/conor)\
**Post date:** [December 3, 2024, 10:59am UTC](https://community.omni.co/t/how-to-create-a-measure-that-is-blank-for-certain-levels-of-aggregation/194/1 "2024-12-03T10:59:08Z")

</div>

## 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.

 ![Financial reporting at a quarterly level](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/266326b5f55b1d8140bb1c60be12e6d7c248f1fc.png)

We define `Sale Price QoQ%` as follows, using a flattened pivot from Period over Period analysis and Snowflake’s [div0](https://docs.snowflake.com/en/sql-reference/functions/div0) function:

```sql
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:

```sql
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.

 ![An example of where the QoQ% should not be displayed as the calculation is confusing at a yearly grain.](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/3cfa5b6e88ede05f7bc45f704c01d071a1ab57e0.png)

### 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:

```sql
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.

 ![An example of where the QoQ% is blank at a yearly time grain.](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/ff22da71095f1bfe57125ac71178d5259f7d9ba1.png)

## 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.