LinkedIn
Twitter
I am building a dashboard where I want to compare numbers for one store versus the rest of the stores.
I’ve seen quite a few examples like this
filter: store_for_comparison {
type: string
group_label: "Store Comparison"
suggest_dimension: stores.name
}
dimension: store_comparison_vs_stores_in_tier {
type: string
group_label: "Store Comparison"
sql: CASE
WHEN {% condition store_for_comparison %} ${name} {% endcondition %} THEN CONCAT('1- ',${name})
WHEN ${store_tiering.tier_id} = (SELECT tier_id FROM ${store_tiering.SQL_TABLE_NAME} WHERE {% condition store_for_comparison %} store_name {% endcondition %} LIMIT 1) THEN ${name}
ELSE NULL
END;;
}
I understand the gist of what’s going on in the example
I have a dashboard, Store Deep-Dive. It has a filter called Store Name based on store.name
After researching and trying lots of things I finally came up with this in my model.
I have in my store view this:
dimension: store_comparison {
type: string
sql: CASE
WHEN {{ _filters['store.name'] }} THEN ${name}
ELSE 'Other Stores'
END;;
}
I want my new tile to show total sales of the filtered store versus values for all the other stores combined.
I get an error that I “cannot use -filters in SQL, and I have since found info on this in the doc.
I’m struggling to find what the work around is.
I’ve added this to my model, but I’m not exactly sure what this does, if anything.
filter: store_comparison_filter {
type: string
#group_label: "Store Comparison"
suggest_dimension: store.name
}
I read lots of material in the docs and on the community that suggest that maybe I can use a dimension or a parameter, but I can’t find a complete example.
I hope this question makes sense and someone can give me advice. It seems like this would be common.
