Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaldubai
Creator
Creator

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.


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

    )

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

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

Aggr(YourExpression,item)

View solution in original post

10 Replies
pradosh_thakur
Master II
Master II

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

    )

Learning never stops.
kaldubai
Creator
Creator
Author

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  !!!

Thanks for your replay.

pradosh_thakur
Master II
Master II

I was talking about typo in my code my friend not yours. If you got your answer please close the thread.

Learning never stops.
kaldubai
Creator
Creator
Author

Yea Your suggestion has been tried still not working.

stigchel
Partner - Master
Partner - Master

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

Aggr(YourExpression,item)

luismadriz
Specialist
Specialist

Try the whole thing as a measure

Cheers,

Luis

kaldubai
Creator
Creator
Author

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

stigchel
Partner - Master
Partner - Master

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)

kaldubai
Creator
Creator
Author

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 .