Can I have a filtered measure of type number?

Knowledge Drop

Last tested: Sep 6, 2019

No. Cite Docs.

Explanation

The filter for a filtered measure has to be applied prior to the aggregation. For a number type measure, the aggregation has already been done in the other measures it is referencing in the sql parameter, so there's no way to apply the filter.

For most use cases, you can just use filters in the other measures that your number type measure is referencing, per the doc. One exception to this is a max or min on a date field, where the aggregation must be done in the sql parameter.

Workarounds

  1. Use CASE WHEN inside the measure to emulate filtering. The below example is a MAX of created_date which is filtering on the field status having the value complete :
    MAX(CASE WHEN status = 'complete' THEN created_date ELSE NULL END)
  2. Perform the filtering in other measures that are not of type number.
    measure: count_complete {

    type: count

    filters: {

    field: status

    value: "complete"

    }

    }



    measure: count_total {

    type: count

    }



    measure: percent_complete {

    type: number

    sql: ${count_complete}/${count_total} ;;

    }

This content is subject to limited support.                

Version history
Last update:
‎04-05-2021 03:14 PM
Updated by: