Calculated Dimension

[AdrianS1]

Hi, I want to create a calculated dimension like this :

=if(DIV1='Skon'  AND
Rank(num(
Sum({<MonthId = {$(=MaxString(MonthId))}>} TotalValueValuatedStk)/1000
,'# ##0,00'))<='30' ,DIV4)

Qlik says the syntax is OK but then when I apply it show invalid dimenion

I want to see in rows  all the  Div4 positions in Div1=Skon and 30 top by totalvaluevaluatedstk in actual mont.

Where is the mistake?

[marcus_sommer]

Such logic needs to be wrapped with an aggr() to give the calculation a dimensional context. Therefore you may try it in this way:

aggr(if(
Rank(
Sum({<MonthId = {$(=MaxString(MonthId))},
               DIV1={'Skon'}>} TotalValueValuatedStk))<=30 ,
DIV4),
DIV4)

[brunobertels]

Hi 

Try this may be 

Aggr(

=if(DIV1='Skon'  AND
Rank(num(
Sum({<MonthId = {$(=MaxString(MonthId))}>} TotalValueValuatedStk)/1000
,'# ##0,00'))<='30' ,DIV4)

,DIV4) 

[Oleg_Troyansky · Accepted solution]

In addition to the suggested fix (using AGGR() for a calculated dimension), I'd recommend moving this condition into the Measure in a form of advanced Set Analysis, to avoid the calculated dimension - it would perform a lot better, especially in a large data set.

I'm teaching all of these methodologies - AGGR(), Set Analysis, and Performance, at the Masters Summit for Qlik . See if you can join us this fall in Dublin or in Orlando - you will learn a lot of advanced material!

Cheers,