I have a dimension (StageName) with 10 or so possible values. There are a few different combinations in which a user might want to filter these that are not mutually exclusive, so I’m trying to use a parameter that lists out different filter combos for the user to select.
I’m trying to create a yesno field something like this:
{%if param._param_value == ‘Option1’ and ${StageName} not in (‘Won’,’Lost’)%} true
{%if param._param_value == ‘Option2’ and ${StageName} not in (‘Pre-Qualified’)%} true
etc..
{%endif%}
The idea is the user can select a single option and I can use the yesno field to filter out multiple values in the StageName making it easier for users.
This syntax doesn’t accept the StageName dimension and if I try it as a regular sql statement it doesn’t accept the liquid param.
Is this even doable? Any advice?
Would adding the fields to the ‘Select filters to update when this filter changes’ help narrow down the results easier?
It's interesting because it's seen in multi-input.
It seems to be a mixture of conditional branching and specific processing.
Can you explain input, proces and output in detail?
@emoxtom-1637169 So I managed to do this with a mix of LookML and a custom dimension on my Look. In the view file, I have :
parameter: pipeline_view {
type: unquoted
allowed_value: {value: "Won"}
allowed_value: {value: "Lost"}
allowed_value: {value: "Openinc"}
allowed_value: {value: "Openexc"}
dimension: pipe_filter {
type: string
sql: {%if pipeline_view._parameter_value == 'Won' %} 'Won'
{%elsif pipeline_view._parameter_value == 'Lost' %} 'Lost'
{%elsif pipeline_view._parameter_value == 'Openinc' %} 'Openinc'
{%elsif pipeline_view._parameter_value == 'Openexc' %} 'Openexc'
{% endif %};;
}
Then in my custom dimension, I have a case statement like:
if((${sfdc.pipe_filter} = "Won" AND ${sfdc.stage_name} = "Closed Won") OR
(${sfdc.pipe_filter} = "Lost" AND ${sfdc.stage_name} = "Closed Lost") OR
(${sfdc.pipe_filter} = "Openinc" AND ${sfdc.stage_name}!="Closed Won" AND ${sfdc.stage_name}!="Closed Lost") OR
(${sfdc.pipe_filter} = "Openexc" AND ${sfdc.stage_name}!="Closed Won" AND ${sfdc.stage_name}!="Closed Lost" AND ${sfdc.stage_name}!="Pre-Qualified"),yes,no)
I then use this dimension as a filter on my visualization. So when a user makes a selection on the parameter, this boolean field is reevaluated and filters my data accordingly.
What I’d like to do though, is avoid having to create a string dimension altogether and create the yesno filter directly on LookML using a combination of the param and the stage_name dimension
1st: I don't think the parameter value will be inserted directly into the sql branch. The parameter value is used to ask questions about sql branching.
2st: I think dynamic field (stage_name) can be used here.
# dynamic dimension using templated filters
# filterの条件により主要なカテゴリーが抽出されそれ以外はその他に分類される
filter: select_category {
type: string
suggest_explore: order_items
suggest_dimension: products.category
}
dimension: category_comparison {
type: string
sql:
CASE
WHEN {% condition select_category %} ${category} {% endcondition %}
THEN ${category}
ELSE 'All Other Categories'
END
;;
}
Did you get a hint?
Isn’t this kind of what I’ve already done? In my LookML, I’m creating a parameter and a matching dimension. I’m then comparing that dimension to a native field (stage_name) in my data to filter accordingly. You seem to be doing the same thing but using a filter instead of a param.
In the end, I’m still having to do the actual data filtering inside my explore.
Hi Nikita!
It looks like that you want a combination of all possible matches between ${sfdc.pipe_filter} and ${sfdc.stage_name} .
I suggest creating a dimension thatcombines all possible values with a derived table and then joining this result with your original explore.
It helped me in one dashboard selection combo box.
Hope it helps!
Regards,
Leo