topic Re: "Calculated dimension" becomes unselectable/a measure? in App Development

"Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 10:39:56 GMT

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?

Re: "Calculated dimension" becomes unselectable/a measure?

Kushal_Chawda — Wed, 04 Dec 2024 11:06:48 GMT

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

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 11:12:58 GMT

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

Can select Casey when we only handle NY.

Re: "Calculated dimension" becomes unselectable/a measure?

Kushal_Chawda — Wed, 04 Dec 2024 11:36:35 GMT

@L_VN what is expression used for both cities?

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 11:38:28 GMT

Both cities:

Only NY:

=Pick(Match("Show Dimension", 'All', 'Works in NY', 'Works in LA'), Name ,if(Aggr(Sum({<Factstype= {'New York'}>}Measure), Name)>0, Name) )

Re: "Calculated dimension" becomes unselectable/a measure?

Kushal_Chawda — Wed, 04 Dec 2024 11:43:05 GMT

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

Re: "Calculated dimension" becomes unselectable/a measure?

marcus_sommer — Wed, 04 Dec 2024 11:47:48 GMT

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.

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 11:49:39 GMT

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 11:52:23 GMT

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.

Re: "Calculated dimension" becomes unselectable/a measure?

Kushal_Chawda — Wed, 04 Dec 2024 11:57:08 GMT

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

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Wed, 04 Dec 2024 12:00:59 GMT

Show Dimension should have always one value selected

Re: "Calculated dimension" becomes unselectable/a measure?

Kushal_Chawda — Wed, 04 Dec 2024 12:27:28 GMT

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

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Tue, 10 Dec 2024 12:49:52 GMT

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

Re: "Calculated dimension" becomes unselectable/a measure?

L_VN — Tue, 10 Dec 2024 12:53:49 GMT

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.