Set expression works as a measure but not dimension

[alandilworth]

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!

[Seyko]

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

[alandilworth]

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:

[Seyko]

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

[alandilworth]

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.

[MadiF]

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

[alandilworth]

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.

[MadiF]

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

[alandilworth]

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!

[mjht]

"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 🤔