11 Replies Latest reply: Jan 9, 2017 7:03 PM by Samuel Lin RSS

    Create a customized dimension for customers who purchased in 2015 but not 2016?

    Samuel Lin

      Hello Qlikers!!

       

      I am trying to create a customized dimension to segment out customers who have purchased in both 2015 and 2016, and 2015 only.

       

      right now, I have

      -------------------

       

      Aggr(

       

        If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

        aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)>0,

          Dual('2015&2016', 1),

         

        If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

        aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)=0,

        Dual('2015&-2016', 2)

       

        ))

      , MasterContactID)

       

      -----------------

       

      it's not working properly, not sure if it's because I have two aggr() function in each if()?

       

      superly appreciate your help~

      Samuel