Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
AdrianS1
Contributor III
Contributor III

Calculated Dimension

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?

Labels (2)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

View solution in original post

3 Replies
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
Master
Master

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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,