Question

How to utilize the value of a dashboard filter

  • 6 January 2022
  • 5 replies
  • 40 views

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.


5 replies

Userlevel 6
Badge +1

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.  

Reply