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