topic Re: Set expression works as a measure but not dimension in App Development

Set expression works as a measure but not dimension

alandilworth — Wed, 12 May 2021 15:59:46 GMT

Hello, I am having an issue with a expression I am using for a dimension. The expression returns the error invalid dimension when used as a dimension, but works fine as a measure. The expression is below. The part highlighted in red is giving me the error. I noticed if I use Total here the error goes away, however, I get incorrect results.

=IF(GetSelectedCount(MonthsActive) = 0, [EntityTerritory], IF(MAX({1<EntityTerritory=>}MonthsActive) < MAX(Total{$<EntityTerritory=>}MonthsActive), null(), EntityTerritory))

Any help will be greatly appreciated. Thanks!

Re: Set expression works as a measure but not dimension

Seyko — Wed, 12 May 2021 16:39:05 GMT

Hello Alan,

With some data set it would be more easiest for us to help you! Random walues would be sufficient to test expression.

Regards

Re: Set expression works as a measure but not dimension

alandilworth — Wed, 12 May 2021 17:40:25 GMT

I have a MonthsActive column in my data table and a filter pane based on the MonthsActive column. I am trying to prevent a row from displaying if the maximum value of MonthsActive for the EntityTerritory in the data table is less than the MonthsActive selection in the filer pane. For example, if the user selects 5 in the Months Active filter pane, only Terr1 should show up since all of the other EntityTerritorys have MonthsActive values less than 5. I hope this makes sense if not I am happy to elaborate.

A sample of my data table is here:

Re: Set expression works as a measure but not dimension

Seyko — Wed, 12 May 2021 18:17:15 GMT

I just remembered now. According to my knwoledge of Qlik , when you use an aggregation function in your expression, Qlik considers automatically the result like a measure (hence the error message).

If you need to perform this kind of transformation, do it within the script editor.

Regards

Re: Set expression works as a measure but not dimension

alandilworth — Wed, 12 May 2021 19:30:53 GMT

Is there any workaround. Doing this in the script editor will not work for my use case because my function needs to take in values from the filter pane. I am trying to make the chart dynamic with filter selections. I can use set analysis in my chart, for some reason it does not like the part highlighted in red below

=IF(GetSelectedCount(MonthsActive) = 0, [EntityTerritory], IF(MAX({1<EntityTerritory=>}MonthsActive) < MAX(Total{$<EntityTerritory=>}MonthsActive), null(), EntityTerritory))

If I change the part in red to MAX(Total{1<EntityTerritory=>}MonthsActive) I no longer receive an error (however, I get incorrect results). Also, if I use this in a measure it returns correct results.

Re: Set expression works as a measure but not dimension

MadiF — Thu, 13 May 2021 00:41:57 GMT

@alandilworth If your visual is a table set up like the mock table in your above post, my suggested workaround would be:

1. Build table with Territory as column 1 and months active as column 2 (the order will be important for next steps to work properly).

2. Create variable vMonthsActive, default value can be to whatever is appropriate for your case (0 is always a good choice)

3. replace Months Active filter pane with Variable Input (slider format), min and max values of your choice (based on min or max months active possible)- assign the variable you just made to the slider

4. In your table, in the 'Entity Territory' Dimension, add a limitation from the drop down --> relative value --> greater than or equal to $(vMonthsActive); since months active is your second column it should compare this condition to that column, so it'll limit what shows based on whether months active is greater than or equal to their slider selections

-Hope this helps!

Re: Set expression works as a measure but not dimension

alandilworth — Thu, 20 May 2021 14:45:16 GMT

Thank you for your response MadiF. Unfortunately I failed to mention one important piece to this. In my use case the user needs the ability to select multiple consecutive values for months active. For instance, in the table below, if the user selects 4 and 5 for months active in a filter pane, only Terr1 and Terr4 should be displayed.

Re: Set expression works as a measure but not dimension

MadiF — Thu, 27 May 2021 19:52:27 GMT

@alandilworth In that case, wouldn't a regular filter pane execute the function you're looking for? A filter pane for the field months active with a selection of 4 and 5 only would in theory perform said function- and if the variable input mentioned in my previous response was defaulted to 0, then the limitation wouldn't affect results and the filter pane would work as expected. Do my previous response paired with a normal filter pane not achieve the desired response?

From your initial response to Seyko it seemed like you were looking for a greater than or less than sort of evaluation, but this most recent response sounds like the effects of multiple selections in a regular filter pane. Apologies, just trying to clarify your goal!

Re: Set expression works as a measure but not dimension

alandilworth — Thu, 27 May 2021 20:22:42 GMT

Thanks for sticking with me, I am still struggling with this. I am using a filter pane, but it is not returning desired results. I currently am using a filter pane with 'MonthsActive' as the dimension. My problem is I want only want the 'Territory' to display if it contains values for every 'MonthsActive' selected.

For example:

Territory          Months Active
A                          1
A                          2
A                          3
A                          4
B                          1
B                          2
C                          1
C                          2
C                          3

If the user selects 1,2,3,4 in my 'MonthsActive' filter pane it will return Territory A, B, and C. However, I want it to only return Territory A since it includes every selection in the filter pane. Basically, I am wanting the filter pane to use 'and' logic instead of 'or' logic where it only returns rows that contains every value in the selection.

In the expression below I am trying to accomplish this with the following logic. I get the maximum 'MonthsActive' value chosen in the filter pane and compare it to the maximum 'MonthsActive' value in the table in the data model. If the maximum 'MonthsActive' value in the data model is less than the max value chosen in the filter pane, the Territory should not display. This way it only display rows that contain every value in the selection. Of course this is not working and returns Invalid Dimension due to the part highlighted in red.

=IF(GetSelectedCount(MonthsActive) = 0, [EntityTerritory], IF(MAX({1<EntityTerritory=>}MonthsActive) < MAX(Total{$<EntityTerritory=>}MonthsActive), null(), EntityTerritory))

Thanks!

Re: Set expression works as a measure but not dimension

mjht — Thu, 27 May 2021 21:59:18 GMT

"If the user selects 1,2,3,4 in my 'MonthsActive' filter pane it will return Territory A, B, and C. However, I want it to only return Territory A since it includes every selection in the filter pane. Basically, I am wanting the filter pane to use 'and' logic instead of 'or' logic where it only returns rows that contains every value in the selection."

You could calculate a field in your load script that contains the max months active for each territory and make use of that field.

Entity Territory Months Active Date WAC Max Months Active

Terr1 1 1/1/2021 100 4

Terr2 1 1/1/2021 234 3

I don't understand the data so this is a guess - because how does Terr1 have months active 1 on Jan 1, 2021,

and months active 2 on Jan 3,2021, months active 3 on Jan 4, 2021 .... its the same month 🤔

Re: Set expression works as a measure but not dimension

alandilworth — Fri, 28 May 2021 00:02:00 GMT

I made that table really quick as dummy data since my work prohibits sharing the actual data I’m using. You can disregard the date column. In my load script I am assigning a months active value to every row in the data table that represents a transaction. I essentially have a table representing every sale/transaction with MonthsActive assigned to each row. It represents how long the territory has been active at the point of the transaction.

IF(GetSelectedCount(MonthsActive) = 0, [EntityTerritory], IF(MAX({1<EntityTerritory=>}MonthsActive) < MAX(Total{$<EntityTerritory=>}MonthsActive), null(), EntityTerritory))

The part highlighted in red the reference to the maximum MonthsActive value in the data table. MonthsActive is a column in my data table that I am using as a dimension for my filter pane.

A little more context: This is for my company that operates on a franchise model. I am trying to make a report showing the sales of each franchise territory according to how many months they have been active. This is why it is important for the filter pane to only return rows that include every selection in my MonthsActive filter pane since it can skew the data if done otherwise.