Looker yes/no dashboard filter should calculate a percentage of a dimension.

[leviMart]

Hello,

In Looker, I have a dashboard with a yes/no filter called "warranty_covered".
There is one tile, call it cs_cost, where the filter should apply to.

However, this table doesn't have a warranty_covered field by default. To calculate the cost_sum in warranty andcost_sum out of warranty, I have a warranty_covered_percentage dimension.

I now want to enable the filter to apply this percentage to a dimension called warranty_adjusted_cs_cost.

Eg if the dashboard filters = "Yes", the warranty_adjusted_cs_cost should multiply the cost_sum with the warranty_covered_percentage.

Any idea how this can be done?

This is my current LookML:

parameter: warranty_covered {
type: yesno
allowed_value: {
value: "yes"
}
allowed_value: {
value: "no"
}
allowed_value: {
label: "All"
value: "All"
}
default_value: "All"
}

measure: warranty_adjusted_cs_cost{
type: number
sql: CASE WHEN {% parameter warranty_covered %} = TRUE THEN ${cost_sum} * ${warranty_covered_percent}
WHEN {% parameter warranty_covered %} = FALSE THEN ${cost_sum} * (1 - ${warranty_covered_percent})
ELSE ${cost_sum} END ;;
}

measure: warranty_covered_percent {
type: average
sql: ${TABLE}.warranty_covered_percent ;;
value_format_name: percent_2
}

measure: cost_sum {
type: sum
value_format_name: eur
sql: ${TABLE}.cost ;;
}

[marcwo]

Hi @leviMart 

First of all:
Do you want to have a parameter that only allows yes/no or a parameter that allows yes/no/all? If the latter, you cant set the type to yesno.

Lets assume you want only yes and no as possibilities, this works:

parameter: covered {
  type: yesno
}

dimension: adjusted_cost {
  sql: CASE WHEN {% parameter covered %} THEN ${TABLE}.cost * {percent}
       WHEN NOT {% parameter covered %} THEN ${TABLE}.cost * (1-{percent})
       ELSE ${TABLE}.cost END;;
}

measure: cost_sum {
  type: sum
  sql: ${adjusted_cost};;
}

[leviMart]

I indeed would like yes/no/all if possible?
Do you have an idea of the option in that case?

[marcwo · Accepted solution]

Hey

then its not a yesno parameter anymore, but type: string and you need to account for the actual values in the CASE WHEN statement.

 parameter: covered {
    hidden: no
    type: string
    allowed_value: {
      value: "yes"
    }
    allowed_value: {
      value: "no"
    }
    allowed_value: {
      value: "all"
    }
    default_value: "all"
  }

  dimension: adjusted_cost {
    sql: 
    CASE 
    WHEN {% parameter covered %} = 'yes' THEN ${TABLE}.cost * 2
    WHEN {% parameter covered %} = 'no' THEN ${TABLE}.cost -1
    WHEN {% parameter covered %} = 'all' THEN ${TABLE}.cost * 1000
    END
    ;;
  }

measure: cost_sum {
  type: sum
  sql: ${adjusted_cost}
}

[leviMart]

Thanks, I think I would need to change the filter for every other dataset this filter is used for in the dash.
I'll go for the first solution and just add the full cost as an extra fixed value.

Thanks again!

[marcwo]

You are welcome!