1 Reply Latest reply on Aug 1, 2018 3:13 PM by Patrick A Van Der Hyde

    Calculate new / lost customers over period

    Romaric PERRONNET

      Hello,

       

      I've been browsing this forum and the whole web looking for an answer but I can't achieve what I would like to do. There are many subjects about it but none seems to answer my needs.

       

      DATA:

       

      The data I have is about investment funds so my information is like the table below. Lets say it ranges from year 2010 to year 2015

      Name of the fund
      Value beginning of period
      Value end of periodYear
      Fund A0200 000 0002011
      Fund A200 000 000250 000 0002012
      Fund A250 000 000300 000 0002013
      Fund B0100 000 0002013
      Fund B100 000 000150 000 0002014
      Fund B150 000 00002015
      Fund C100 000 000150 000 0002010
      Fund C150 000 000200 000 0002011
      Fund C200 000 000200 000 0002012
      Fund C200 000 000200 000 0002013
      Fund C200 000 000200 000 0002014
      Fund C200 000 000200 000 0002015

       

      As you can see, at the fund level, end of period year N = beginning of period year N + 1

       

      To this table, I made a join to another table which give me, for each fund, how much the client owns at the end of the period.

      For Fund A, I'll have client 1 = 20%, client 2 = 50%, client 3 = 30%, and this for each fund. I'm then able to say that client 1 owned 0€ in begenning of 2011 and 40 000 000 end of 2011.

       

      REQUEST:

       

      Let's imagine client 4 arrives in 2012 and buys out the 20% of client 1. Client 4 will then start at 40 000 000€ begenning of period and 50 000 000€ end of period, while client 1 will start 2012 at 0€.

       

      The table should then tell me that I have 1 new client and 1 lost client.

       

      My first idea was to say that Lost client

      SUM

      (

      IF { FIXED [Client name],[Year] : SUM([AuD EoP by client]) } = 0

      and { FIXED [Client name],[Year] : SUM([AuD BoP by client]) } > 0

              THEN -1

      END

      )

       

      And Won client

      SUM

      (

      IF { FIXED [Client name],[Year] : SUM([AuD BoP by client]) } = 0

      and { FIXED [Client name],[Year] : SUM([AuD EoP by client]) } > 0

              THEN 1

      END

      )

      However, in the case shown above, it doesn't work ... Client 4 won't appear as a new client because he has AuD at the beginning of the period. To make sure I have the full scope, I need to compare it to previous year and say that:

       

      A won client in period N is a client who had 0 at the beginning of period N and > 0 at the end of the period N OR had 0 at the end of period N-1 and >0 at the beginning of period N

       

      A lost client in period N is a client who had 0 at the beginning of period N and > 0 at the end of the period N OR had 0 at the end of period N-1 and >0 at the beginning of period N

       

      I also need to integrate a check to make sure that the extremity aren't taken into account. It's not because my data starts in 2010 that all my clients arrived in 2010.

       

      I also tried to used the MIN / MAX method with the following code :

       

      Client first acquisition

      { FIXED [Client name]:MIN(IF[AuD EoP by client]>0 then [Year] END)}

       

      Client last disposal

      { FIXED [Client name]:MAX(IF[AuD EoP by client]=0 then [Year] END)}

       

      First acquisition seems to work fine. Last disposal however, not so fine. Since I'm using %, value * 0% = 0 and not "null". So if a client leaves in 2012, he'll value = 0 in 2013, 2014 and 2015. Tableau says that the client left in 2015.

       

      Ultimately, what I would like to achieve is something like this

       

      Type 201020112012201320142015
      Number of client200210220230240250
      Lost client vs previous year10322015
      Won client vs previous year2013123025

       

      I would love to include a copy of packaged workbook but data includes name of the client, value of the asset, fees they pay and margin we make on the products so ... It's pretty confidential.

       

      Thank you in advance,

      Romaric