2 Replies Latest reply on Sep 11, 2012 11:37 PM by Wilson Po

    Calculate New Customers

    Ricardo Mota

      Hello

       

      I want to Count the new customers in a period. (for example this year)

       

      I create a calculated field (from a forum post) with:

       

      Days Since First = DATEDIFF('day',MIN([Date]),now())

       

      After that I have created a:

       

      old/new customer =

      IF [Days Since First] > DATEPART('dayofyear',today()) THEN

          "Old"

      ELSE

          "New"

      END

       

      After I have created a

       

      New Customer=

      IF [ old/new customer] = "New" then 1 else 0

      END

       

       

      Now I want to count the New Customers. How can I do that?

       

      When I Put the Client ID in the Rows it works, but I just want the count of new customers.

       

      Thanks

        • 1. Re: Calculate New Customers
          Mark Holtz

          Hi Ricardo,

           

          Can you post your workbook?

           

          My initial thought is that using the MIN function in your Days Since First makes the entire calculation dependent on being aggregated.

          Perhaps another way to accomplish this would be somehow using a parameter to set a date threshold and then creating calculated fields based on customer. 

          • 2. Re: Calculate New Customers
            Wilson Po

            Hi Ricardo,

             

            Mark's right in that the MIN() aggregation is actually what is causing the whole set of calculations to be aggregate.  As a result we need to include the Client ID in the view as that is the only means to evaluate what is new or not at the customer level.  In this situation a table calculation can help out with this:

             

            1) Create a calculated field: WINDOW_SUM([New Customer])

            2) Move Client ID to the level of details, this will overlap the marks but this is desired

            3) Use the new field on the text shelf, Right-click on it>edit table calculation to "compute using" [Client ID]

             

            This should give you the correct sum of new customer with some overlapping of the numbers.  It might take some tinkering if you desire to add more dimensions in the view.

             

            Hopes this help!

            Wilson