9 Replies Latest reply: Aug 15, 2016 4:09 PM by Vineeth Pujari RSS

    lost customers

    Ali Hijazi

      Hello

      I have the following pivot table in which I'm counting the number of customers who got invoices transactions:

      count(distinct {<TRANS_TYPE={'invoice'}>} CUSTOMER_KEY)

      community.png

      what I want now is to add another column which counts the lost customers per month; a lost customer is the one who hasn't got any invoice transaction in the period 12 months prior to each month in the pivot table

      so let's take the first business unit (scrambled here) now next to Jan 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between 1 Feb 2015 and 31 Dec 2015

      next to Feb 2016 I want to get the count of customers who haven't got any invoice transaction in the period which is between  1 Mar 2015 and 31 jan 2016 and so on

       

      my problem is in how to get the variable date range per row which depends on the month displayed

      so in peudo code the lost customers are the ones excluded in the range of dates which depend on the displayed month year

       

      please advise