2 Replies Latest reply: Jan 25, 2016 12:26 PM by Marcelo Reperger RSS

    Aggr with IF and Distinct question to get Attrition

      I'm trying to get the attrition of our users and I can't manage to get it using Aggr. I would like to know which users were active a year ago and check if they are still active now, I'm trying to do that within a dynamic variable in order to see dynamic results when I applying filters to QV.

       

      Here is a possible aproach.

       

      I have the following data:

       

      order iduser iddate
      11111112015-01-01
      21111112016-01-06
      31111112016-01-04
      42222222016-01-01

       

      To get attrition I need to get Amount of users who used to buy a year ago and Amount of users who used to buy a year ago and buy now si:

       

      Amount of users who used to buy a year ago and buy now / Amount of users who used to buy a year ago = 1 - Attrition

       

      So lets focus on "Amount of users who used to buy a year ago and buy now". I'll add <max purchase date> as pseudocode, I've tried to solve this using Aggr everywhere but can't find the solution.

       

      =COUNT( distinct (  IF(  [date] <= date(AddMonths(Now(),-13),'YYYY-MM') AND [date] >= date(AddMonths(Now(),-21),'YYYY-MM') AND <max purchase date> >= date(AddMonths(Now(),-9),'YYYY-MM'),  user_id,  NULL  )  ))

       

      I believe if I manage to create this structure I'll be able to solve it:

       

      order iduser iddatemax(date)
      11111112015-01-012016-01-06
      21111112016-01-062016-01-06
      31111112016-01-042016-01-06
      42222222016-01-012016-01-01

       

      I don't want to make a script at the loading process as I want to get these results dynamically so I've been trying to use Aggr within the previous query but I can't manage to get the Aggr working without No-Aggr data as I need every order date with the MAX(date) in order to add the conditional.

       

      How can you Group data as if I were using a Join at MySQL.

       

      Maybe there's a workaround.

       

      I've calculated max(date of order) for each user while loading data but of course that info is not dynamic, and so far this is how I've managed get a static attrition calc:

       

      =COUNT(distinct (IF([Order Month] <= date(AddMonths(Now(),-13),'YYYY-MM') AND [Order Month] >= date(AddMonths(Now(),-21),'YYYY-MM') AND [Last Order Month] >= date(AddMonths(Now(),-9),'YYYY-MM'),user_id, NULL))) / COUNT(distinct (IF([Order Month] <= date(AddMonths(Now(),-13),'YYYY-MM') AND [Order Month] >= date(AddMonths(Now(),-21),'YYYY-MM'),user_id, NULL)))

       

      This works but I can't apply dynamic filters as [Last Order Month] is assigned to each user from scratch.

       

      Any idea is welcome.

        • Re: Aggr with IF and Distinct question to get Attrition
          Oleg Troyansky

          Hi Marcelo,

           

          I'm not sure why do you think  you need AGGR for this solution... I believe you need Set Analysis with the use of Advanced Search to untangled your condition.

           

          So, you need to COUNT your Users, but only those that purchased from you a year ago , except for those that are still purchasing. So, your Set Analysis condition could look like this (you'll need to fill in the specific filters):

           

          COUNT( {<USER = P({<condition that signifies purchases a year ago>} USER)

                                          - P({<condition that signifies purchases this year>} USER) >}   USER)

           

          I teach similar use cases for Set Analysis in my advanced lecture at the Masters Summit for Qlik. You can also read about it in my new book QlikView Your Business.

           

          cheers,

          Oleg Troyansky

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

          • Re: Aggr with IF and Distinct question to get Attrition

            Thanks for your reply Oleg.

             

            I've tried this way but it seems the filter does not work as expected.

             

            If I apply the filter to the listbox I get a different result.

            I've read there are some issues when using dates.

             

            If I filter the data using the UI I get a result, but when I try to do it in the listbox I get a different one:

             

            =[Order Month] <= date(AddMonths(Now(),-13),'YYYY-MM') AND [Order Month] >= date(AddMonths(Now(),-21),'YYYY-MM')

             

            The thing is that If I do the same at the var panel the query works fine:

             

            =

            COUNT(

              distinct (

              IF(

              [Order Month] <= date(AddMonths(Now(),-13),'YYYY-MM') AND

              [Order Month] >= date(AddMonths(Now(),-21),'YYYY-MM'),

              user_id,

              NULL

              )

              )

            )

             

            Result = 10,000 users

            Manual filter using the UI = 10,000 users

            Listbox filter = 8,000 users

             

            That's why I can't set the Bookmark to do the Set Analysis