Conditional Guage Max values

[Suspop]

Hi all, 

Brand new to qliksense so appreciate this may be very simple. But I would like to make a basic guage that adjusts the max value based on what filters are selected - will use teams for the example. Reason for this is obviously if you filter for one team the max could be 100 another team could be 50 but when you filter for both together the limit should be 150. 

Managed to get it to return the 100 and 50 in this case but couldn't manage to get the max value to go to 150 when both teams are selected it just goes to 200 rather than the 150 desired.

Current code = 

=if( Teamname = 'Team1' and Teamname= 'Team2',
150,
if(Teamname = 'Team1', 100, if(Teamname= 'Team2', 50, 200))
)

Also I have a team name which has an apostrophe in it and that obviously makes it difficult so if there is a quick fix for that then also would be very helpful.

Thanks!

[LRuCelver · Accepted solution]

If the logic behind the max value for multiple teams is supposed to be the sum of the max values per team, I would recommend adding a new field for these values per team. You can than use Sum(Capacity) to get the total max. value for all possible teams. Since you are showing 200 when nothing is selected but the total is only 150, I'm assuming the logic is a bit more complex. You could catch edgecases and keep the sum for all others.

If you only have a few teams (ideally <5) you could use an expression to "hardcode" the max value using it:

=If(not IsNull(GetFieldSelections(Teamname)), Pick(Match(GetFieldSelections(Teamname), 'Team1', 'Team2', 'Team1, Team2'), 100, 50, 150), 200)

[Suspop]

Thanks that's worked for me perfectly!