10 Replies Latest reply: Jan 10, 2018 7:59 AM by Piet Hein van der Stigchel

# Calculated Dimension

Hello Qlikers,

I am trying to use the below formula as a calculated Dimension But its giving me invalid dimension.

the scenario im trying to achieve is to create buckets for each item based on two dates

first one :

As_At_Date:which is user input or max date as default

second one:

last_rcv_DATE: which comes from the system.

this was achieved in the script using today date instead of As_At_Date.

=  Dual(

if( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D') >=0 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=60 , '0-60',

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=61 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=120 , '61-120',

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=121 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=180 , '121-180',

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=181 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=365, '181-365',

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=366 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=730, '1-2 YR',

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=731 , '>2 YR')))))),

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=0 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=60 , 1,

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=61 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=120 , 2,

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=121 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=180 , 3,

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=181 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=365, 4,

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=366 and Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') <=730, 5,

if( Interval(max(As_At_Date)- max(DATE(floor(last_rcv_DATE))),'D') >=731 , 6))))))

)

• ###### Re: Calculated Dimension

if you are using it in a table than try this

v_max date =   =max(As_At_Date)

replace max(As_At_Date) as \$(v_max date) in the expression. something like this . check for any typo error

=  Dual(

if( Interval(\$(v_max date) - max(DATE(floor(last_rcv_DATE))),'D') >=0 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=60 , '0-60',

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=61 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=120 , '61-120',

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=121 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=180 , '121-180',

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=181 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=365, '181-365',

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=366 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=730, '1-2 YR',

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=731 , '>2 YR')))))),

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=0 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=60 , 1,

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=61 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=120 , 2,

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=121 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=180 , 3,

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=181 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=365, 4,

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=366 and Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') <=730, 5,

if( Interval(\$(v_max date)- max(DATE(floor(last_rcv_DATE))),'D') >=731 , 6))))))

)

• ###### Re: Calculated Dimension

i place it as a measure and it gives the right buckets against each item, so i guess there is no typo errors .

as for the variable what difference would it make because as far as i can sea  its only going to make the formula simpler  !!!

• ###### Re: Calculated Dimension

Yea Your suggestion has been tried still not working.

• ###### Re: Calculated Dimension

If it works as a measure against each item, try aggregating over item

Aggr(YourExpression,item)

• ###### Re: Calculated Dimension

Try the whole thing as a measure

Cheers,

Luis

• ###### Re: Calculated Dimension

your suggestion worked but it effected the performance , is there anyway we can replace this but with the same result.

• ###### Re: Calculated Dimension

A lot of ifs in a calculated dimension will be slow, maybe this helps a bit?

Dual(

Pick(Alt(

if( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')=0,1),

Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/60,1),1,2,3)

,Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/365,1),4)

,Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/730,1),5)

,6)

,'0-60','61-120','121-180','181-365','1-2YR','>2YR')

,

Alt(

if( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')=0,1),

Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/60,1),1,2,3)

,Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/365,1),4)

,Pick(Ceil( Interval(max(As_At_Date) - max(DATE(floor(last_rcv_DATE))),'D')/730,1),5)

,6)

• ###### Re: Calculated Dimension

I tried this as well, the performance got even worst. the problem actually in the aggr yea its true that nested IF is resource heavy but aggr is the one causing the issue. the thing is i cant put it in the backend as it depends on the user selection or maybe there is a way which i dnt know .

• ###### Re: Calculated Dimension

may be instead of the calculated dimension use a synthetic dimension and move the calculation logic to the expression.Dimension

=ValueList('0-60','61-120','121-180','181-365','1-2YR','>2YR')

and in your expression you can repeat the valuelist like

if(ValueList('0-60','61-120','121-180','181-365','1-2YR','>2YR')='0-60',

The Expression for the first 60 day period,

if(ValueList('0-60','61-120','121-180','181-365','1-2YR','>2YR')='61-120'

..............

(or use a pick(match(...)