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?
The solution I found was to incapsulate the expression in another Aggr. This made it selectable.
@L_VN Could you share a screenshot of your issue? It is not clear that what is not selectable and what is required
Cannot select Casey in case where we handle both NY and LA.
Can select Casey when we only handle NY.
@L_VN what is expression used for both cities?
Both cities:
=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)
)
Only NY:
=Pick(Match("Show Dimension", 'All', 'Works in NY', 'Works in LA'),
Name
,if(Aggr(Sum({<Factstype= {'New York'}>}Measure), Name)>0, Name)
)
@L_VN I am able to select (make sure both are calculated dimension and not measure). Please share sample app to look at issue
A calculated dimension would never behave like a native one else causing more or less side-effects. Therefore such approaches should be an exception if no other way is possible. But in your described scenario the relevant matching respectively categorizing could be also done within the data-model by assigning the cities to the appropriate areas.
If any overlapping logic are intended the normal dimension-table logic might be extended with The As-Of Table - Qlik Community - 1466130.
The example I've given here is a very simplified example. In the real example we have much more complex data model and measures that depend on a myriad of factors making the it unfeasible to calculate everything in the backend.
@L_VN I ran your app, I can see the selection