Knowledge Drop

Can I have a filtered measure of type number?

  • 6 April 2021
  • 0 replies
  • 513 views

Userlevel 5

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.                

 

 


0 replies

Be the first to reply!

Reply