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.
I started writing this answer about how much I would like this to be possible since I can see so many use cases and that derived table or parameter is the only option.. then few tests and I think I’ve got it.
Try this:
filter: store_comparison_filter {
type: string
suggest_dimension: store.name
}
dimension: store_comparison {
type: string
sql: IF({% condition store_comparison_filter %} ${name} {% endcondition %}, ${name}, "Other Stores" ;;
}
I think it will work : )
Thank you for responding. I’m pretty sure I’ve been down this road before. The initial sample I included in my post are similar to yours, only that you use an IF versus a CASE.
My question is how does this store_comparison_filter get populated.
Here is my dashboard with the filter filled in.
If I have the filter update the new tile, then I only get 1 row of output for that store.
Regardless, I always get this
SELECT
CASE
WHEN 1=1 -- no filter on 'store.store_comparison_filter'
THEN store.name
ELSE 'Other Stores'
END AS "store.store_comparison",
COALESCE(SUM(lineitem.sale_price ), 0) AS "lineitem.total_sales"
FROM retail.orders AS orders
LEFT JOIN retail.lineitem AS lineitem ON orders.order_id = lineitem.order_id
LEFT JOIN retail.store AS store ON orders.store_id = store.store_id
GROUP BY
1
ORDER BY
2 DESC
FETCH NEXT 500 ROWS ONLY
It’s just not clear to me how this filter get’s populated.
Here’s my tile elements
I finally figured out that my store_comparison filtelr appears in a separately sorted section of my dashboard tile’s explore. When I set the value there things work.
I still don’t understand how to equate this filter to the “dashboard” filter for store.name.
Store comparison filter does not appear in the filed picker of the dashboard...only the explore.
I have figured out that I populate the filter in the explore.
Still I don’t know how to equate this to a “dashboard” filter.
I think I figured out how to get this working.
I think I struggle a little bit w/ updating my model and then editing dashboards simultaneously. I think, the dashboards are not always seeing the latest changes to my model.