Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
alandilworth
Partner - Contributor III
Partner - Contributor III

Set expression works as a measure but not dimension

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!

10 Replies
alandilworth
Partner - Contributor III
Partner - Contributor III
Author

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.