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?
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);;
}