Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik and ServiceNow Partner to Bring Trusted Enterprise Context into AI-Powered Workflows. Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
L_VN
Partner - Contributor III
Partner - Contributor III

"Calculated dimension" becomes unselectable/a measure?

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?

 

Labels (3)
13 Replies
L_VN
Partner - Contributor III
Partner - Contributor III
Author

Show Dimension should have always one value selected

Kushal_Chawda

@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)
)

 

Screenshot 2024-12-04 at 12.25.06.pngScreenshot 2024-12-04 at 12.25.31.png

 

L_VN
Partner - Contributor III
Partner - Contributor III
Author

The solution I found was to incapsulate the expression in another Aggr. This made it selectable. 

L_VN
Partner - Contributor III
Partner - Contributor III
Author

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.