3 Replies Latest reply: Apr 26, 2018 9:45 PM by Sunny Talwar RSS

    Using a Variable or "Set Analysis" as a dimension filter - Customer Retention

    Vlad Batalovs

      Good Time of Day to you Qlik Gurus,

       

      I've been working with Qlik for a few month and have got a question to ask you guys on the use of variables and filter selection as well as set analysis, so here I go.

       

      This is my current Customer Retention App, with the main focus on the 4 manually defined groups of Customers, namely:

       

      a) Lost

      b) Contracted

      c) Expanded

      d) Gained

       

      I define these statuses in a lengthy Set Analysis:

       

      =if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

      and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]>}[Containers]) > vMinContainers

          and SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]), [Customer combi]),[Customer combi]))

      , 'Expanded',

      if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

      and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]>}[Containers]) > vMinContainers

      and SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) <= SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]), [Customer combi]),[Customer combi]))

          ,'Contracted',

      if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

      and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]) <=0 , [Customer combi]),[Customer combi]))

      ,'Gained',

      if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) <=0

      and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]) > vMinContainers , [Customer combi]),[Customer combi])),

      'Lost'))))

       

      And it works fine:

      Capture1.PNG

      (I use Measure Variables for Status groups in bar charts and due to different dimensions there, they also can't be clicked to select a separate status)

       

      What I want to do here, rather then making a selection in my table, I would like to have a drop down above the table to be able to select each or any of these predefined categories, so all the the other elements of the app will only display the info of selected category as per below:

       

      Capture2.PNG

      I hope I am making myself clear, otherwise please let me know if I am not clear.

       

      Thanks in advance,

      Vlad