1 Reply Latest reply: Dec 22, 2016 12:29 AM by Global Digikart RSS

    Set Dimension value based on the Expression values

    Global Digikart

      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
          Global Digikart

          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