Calculated Dimension

[kaldubai]

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.

can any one advise.

=  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))))))

    )

[stigchel]

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(...)