10 Replies Latest reply: Jan 10, 2018 7:59 AM by Piet Hein van der Stigchel RSS

    Calculated Dimension

    khaled ab

      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

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

              )