topic Translate calculated dimension in QlikView

Translate calculated dimension

isaiah82 — Thu, 03 May 2012 15:27:28 GMT

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

Re: Translate calculated dimension

danielact — Thu, 03 May 2012 15:53:07 GMT

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.

Translate calculated dimension

isaiah82 — Fri, 04 May 2012 13:57:34 GMT

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