Translate calculated dimension

[isaiah82]

Say I have a calculated dimension that returns one of four values: 1,0,-1, or Null()

The expression looks something like this:  Sign(Aggr(Sum(value),dimension))

I'm looking for a clean way of translating the returned values (again, 1,0,-1, or null) to a meaningful value to the users; e.g. Gainers,Losers,No Change, or Not Applicable.  I know I can do this with nested If statements, but looking for a better/simpler way.  I can't move this into the script because it is dynamic based on selections.

Any ideas much appreciated!  -Isaiah

[danielact · Accepted solution]

Try using a pick/match combination:

pick(match(Sign(Aggr(Sum(value),dimension)),1,0,-1),'Gainer','No Change','Loser',)

You'll need one if statement for the null values, as I don't believe match works with null values.

[isaiah82]

That does it; thanks!  I ended up using RangeMax around the Aggr() to accommodate the nulls (I gave them a value of -2.)