topic Re: Calculated Dimension in App Development

Calculated Dimension

kaldubai — Wed, 03 Jan 2018 09:40:37 GMT

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

)

Re: Calculated Dimension

pradosh_thakur — Wed, 03 Jan 2018 09:54:16 GMT

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

kaldubai — Wed, 03 Jan 2018 10:01:15 GMT

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.

Re: Calculated Dimension

pradosh_thakur — Wed, 03 Jan 2018 10:04:25 GMT

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

Re: Calculated Dimension

kaldubai — Wed, 03 Jan 2018 10:09:16 GMT

Yea Your suggestion has been tried still not working.

Re: Calculated Dimension

stigchel — Wed, 03 Jan 2018 10:56:10 GMT

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

Aggr(YourExpression,item)

Re: Calculated Dimension

luismadriz — Wed, 03 Jan 2018 11:02:00 GMT

Try the whole thing as a measure

Cheers,

Luis

Re: Calculated Dimension

kaldubai — Mon, 08 Jan 2018 07:19:53 GMT

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

Re: Calculated Dimension

stigchel — Mon, 08 Jan 2018 17:32:59 GMT

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

kaldubai — Wed, 10 Jan 2018 12:17:05 GMT

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

stigchel — Wed, 10 Jan 2018 12:59:21 GMT

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