Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))))))
)
If it works as a measure against each item, try aggregating over item
Aggr(YourExpression,item)
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))))))
)
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.
I was talking about typo in my code my friend not yours. If you got your answer please close the thread.
Yea Your suggestion has been tried still not working.
If it works as a measure against each item, try aggregating over item
Aggr(YourExpression,item)
Try the whole thing as a measure
Cheers,
Luis
your suggestion worked but it effected the performance , is there anyway we can replace this but with the same result.
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)
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 .