Question

Creating yes/no type dimension with measure condition in sql

  • 1 July 2021
  • 1 reply
  • 17 views

I have a location dimension and a inventory measure.

I want to create a yesno type dimension which removes a set of locations and shows inventory greater than 0.

 

I tried to create a dimension as follows

 

  dimension: loc_inv_filter {
    label: "Loc ID VWH and inv greater than 0"
    type: yesno
    sql:${d_org_loc_lu.loc_id_desc} NOT IN ('Loc 1', 'Loc 2', 'Loc 3', 'Loc 4') AND ${inventory} > 0;;
  }

 

But it seems like we cannot use the measure of aggregation type in a dimension. What is the work around for this. I saw an article for dimensionalizing a measure but it is too complicated. Is there any other way?


1 reply

Your SQL needs to be an IF statement. Not knowing your database, I’ll just do generic sql.

 

dimension: loc_inv_filter {

    label: "Loc ID VWH and Inv Greater Than 0"

    type: yesno

    sql: IF((${d_org_loc_lu.loc_id_desc} NOT IN ('Loc 1', 'Loc 2', 'Loc 3', 'Loc 4')) AND ${inventory} > 0, 1, 0);;

}

Reply