1 Reply Latest reply: Dec 22, 2016 12:29 AM by Ashmita Sharma RSS

    Set Dimension value based on the Expression values

    Ashmita Sharma

      Hi

       

      I have a pivot table in Qlikview as shown below. I need to set a dimension value based on the gaps in the Year Month selected by the user. If the Gap is >=3, need the dimension to set as Return otherwise the dimension value should be Active.

       

      In short, any customer who is not giving continuous business for more than 3 months to be identified and marked.

       

      12-21-2016 3-32-12 PM.png

      Thanks.,

      Ashmi

        • Re: Set Dimension value based on the Expression values
          Ashmita Sharma

          Just came up with this expression recently yet the requirement is not completely achieved for Return

           

          "Customers who have purchased first and then atleast 3months gap and starting purchasing again is what is required". Need to get 3 month gap between 2 purchases...

           

          =aggr(
          if(count(distinct TOTAL Month)=
          aggr(if(sum(Sales)>0,count(distinct Aggr(distinct Rank(Sum(Sales),4),Customer,Year,Month))
          -(
          count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} Sales),Customer,Year,Month)=0,Month)))),Customer),'Active',
          if((count(DISTINCT TOTAL Month)-(count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} Sales),Customer, Year,Month) > 0,Month))))>=(count(DISTINCT TOTAL Month)/3),
          'Return','Active')),
          Customer)

           

          Pls help me sort this issue

           

          Thanks.,

          Ashmi