Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a case where we want to display certain dimensions based on measure criteria.
We have this backend:
Names:
load * inline [
Name
Alex
Bo
Casey
];
Facts:
load * inline [
Measure, Factstype, Name
1, New York, Alex
1, Los Angeles, Bo
1, Los Angeles, Casey
];
Island:
load * inline [
Show Dimension
All
Works in NY
Works in LA
];
I have the values of Show Dimension in a filter pane, and only the Names that meet the criteria are shown according to this pick/match function:
=Pick(Match("Show Dimension", 'All', 'Works in NY', 'Works in LA'),
Name
,if(Aggr(Sum({<Factstype= {'New York'}>}Measure), Name)>0, Name)
,if(Aggr(Sum({<Factstype= {'Los Angeles'}>}Measure), Name)>0, Name)
)
And this works, only the Names meet the criteria are shown. HOWEVER, they are not selectable (it is as if they are a measure and not a dimension.
If I reduce the pick/match to only take New York into account like this:
=Pick(Match("Show Dimension", 'All', 'Works in NY', 'Works in LA'),
Name
,if(Aggr(Sum({<Factstype= {'New York'}>}Measure), Name)>0, Name)
)
Then Bo and Casey are selectable, but Alex is not since he meets a criteria in the measure, even when 'All' is selected.
The way I interpret it is that Qlik handles the Names whose expressions return a value as a measure and not a dimension.
Is there a way around this?
Show Dimension should have always one value selected
@L_VN Just a little change in Island table
Island:
load * inline [
Show Dimension, Dim
All,1
Works in NY,2
Works in LA,3
];
You can use below calculated dimension
=pick(Dim,
Name,
aggr(Only({<Factstype= {'New York'}>}Name),Name),
aggr(Only({<Factstype= {'Los Angeles'}>}Name),Name)
)
The solution I found was to incapsulate the expression in another Aggr. This made it selectable.
The issue here is that in the real world example we have much more complex expressions that depend on an array of parameters, so implementing the solution only in back-end does not work.