Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kaldubai
Contributor

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
Honored Contributor

Re: Calculated Dimension

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

Aggr(YourExpression,item)

10 Replies
pradosh_thakur
Valued Contributor III

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

    )

kaldubai
Contributor

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

Thanks for your replay.

pradosh_thakur
Valued Contributor III

Re: Calculated Dimension

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

kaldubai
Contributor

Re: Calculated Dimension

Yea Your suggestion has been tried still not working.

stigchel
Honored Contributor

Re: Calculated Dimension

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

Aggr(YourExpression,item)

luismadriz
Valued Contributor

Re: Calculated Dimension

Try the whole thing as a measure

Cheers,

Luis

kaldubai
Contributor

Re: Calculated Dimension

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

stigchel
Honored Contributor

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)

kaldubai
Contributor

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 .