0 Replies Latest reply: Aug 25, 2016 12:13 PM by Marco Meggiorin RSS

    count Loast/New/Loyal customer in Sense

    Marco Meggiorin

      Hello,

      I am New to Qlik Sense, and I have a problem with Count of Lost/loyal/new customers.

      I read several post but I did not find the right solution for QlikSense.

       

      I have 3 information:

      1 Customer name

      2 Year

      3 Value

       

      I need to understand how many customer become lost in 2016 keeping in mind that for me lost customers are whose did not buy since 2013. People who bought in 2014 or 2015 but no in 2016 for me are “sleepy”. I would like to have the total numer.

       

      For the moment, the only formula that helps me is the followig:

      count(distinct aggr(distinct if(sum({<Year={$(=max(Year))}>}Value)=0

      AND sum({<Year={$(=max(Year)-1)}>}Value)=0

      AND sum({<Year={$(=max(Year)-2)}>}Value)=0

      AND sum({<Year={$(=max(Year)-3)}>}Value)>0

      ,Customer),Customer))

       

      BUT the main problem is when I select the year, the count formula doesn’t work and the value become “0”.

      Here enclosed you can find a copy app to help you understand my problem.

      Thanks for your attention.

      Bye

      Marco