3 Replies Latest reply: Jul 5, 2013 6:20 AM by Gysbert Wassenaar RSS

    Calculating Clients Turnover

    Marco Imperiale

      I know there are already many question on it but my question is a little different:

      I need ti calculate the client turn over in grid, graph and textboxes:

      A "New" client is a client that has NumSpot>0 in 2013 but nothing or 0 in 2012

      A "Lost" client is a client that has NumSpot>0 in 2012 but nothing or 0 in 2013

      A "Renew" client is a client that has NumSpot>0 in 2013 and  >0 in 2012

       

      The question is: I make my selections (many fields like period, area, client area......) how many clients are new, lost or renew?

       

      I try with something like this:

       

      i.e. for "Lost" clients (but similar for the other values)

      =count(          { 

                      $<SchedDate_Year={2012}, NumSpot={">0"}>

                      +<SchedDate_Year={2013}, NumSpot={0}> 

                   }            DISTINCT ClientID)

       

      but, doesn't works (because null values and maybe because a wrong sintax).

      Can someone give me a way to get these values?

       

      Thank you very much.

      Mark.

        • Re: Calculating Clients Turnover
          Tresesco B

          You are using '+' operator which is for union, but i guess you have to use '*' for AND/intersection logic, because both the conditions have to be true to get your result. Apart from that NULL values have to be treated separately for your 'nothing'.

          • Re: Calculating Clients Turnover
            Gysbert Wassenaar

            Try:

             

            Lost:

            =count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

             

            New:

            =count({$<SchedDate_Year={2013}, NumSpot={">0"}, ClientID*=E({$<SchedDate_Year={2012}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)

             

            Renew:

            =count({$<SchedDate_Year={2012}, NumSpot={">0"}, ClientID*=P({$<SchedDate_Year={2013}, NumSpot={">0"}>}ClientID)>}DISTINCT ClientID)