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)
1 Solution

Accepted Solutions
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. 

View solution in original post

13 Replies
Kushal_Chawda

@L_VN  Could you share a screenshot of your issue? It is not clear that what is not selectable and what is required

L_VN
Partner - Contributor III
Partner - Contributor III
Author

L_VN_0-1733310725862.png

Cannot select Casey in case where we handle both NY and LA.

L_VN_1-1733310762469.png

Can select Casey when we only handle NY.

Kushal_Chawda

@L_VN  what is expression used for both cities?

L_VN
Partner - Contributor III
Partner - Contributor III
Author

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

@L_VN  I am able to select (make sure both are calculated dimension and not measure). Please share sample app to look at issue

Screenshot 2024-12-04 at 11.42.10.png

marcus_sommer

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.

L_VN
Partner - Contributor III
Partner - Contributor III
Author

 
L_VN
Partner - Contributor III
Partner - Contributor III
Author

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.  

Kushal_Chawda

@L_VN  I ran your app, I can see the selection

Screenshot 2024-12-04 at 11.56.34.png