3 Replies Latest reply on Jan 27, 2014 6:56 AM by philmodjunk

    How to count customers with first sale within a certain week

    med

      Title

      How to count customers with first sale within a certain week

      Post

           I have a customer table and a sales history table linked by customer code.

           The sales history table contains among other fields The sale date, sale type and of course customer code.

           I would like to count the number of customers or possibly produce a list of those who had their first sale of a certain type occurring between two dates. The type of sale and the two dates will be entered on a layout by the user.

           I have tried many ideas but I failed, can any body help me with a hint.

        • 1. Re: How to count customers with first sale within a certain week
          philmodjunk

               I am guessing this relationship:

               Customers-----<SalesHistory
               Customers::__pkCustomerID = SalesHistory::_fkCustomerID

               The key here would be to find only first sales. The key to that can be a self join relationship:

               SalesHistory----<SalesHistory|SameCust
               SalesHistory::_fkCustomerID = SalesHistory|SameCust::_fkCustomerID

               Where SalesHistory|SameCust is my name for a new Tutorial: What are Table Occurrences? of SalesHistory.

               You can then define this calculation field and set it to evaluate from the context of SalesHistory (not SalesHistory|SameCust):

               __pkSalesHistoryID = SalesHistory|SameCust::__pkSalesHistoryID     (You can also use a timestamp or date field instead of a __pk field in most cases)

               Then you can perform a find on SalesHistory specifying a date range for your week and a 1 in this calculation field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: How to count customers with first sale within a certain week
            med

                 Thank you for the reply. But I do not understand how the calculation field will help find the first sale by being searched for the value 1.

                 When you said  __pksaleshistoryid did you mean a sale id or the customer id in that table?

                 And also should the table saleshistory |samecustomer be sorted?

                 May be I need more detailed explanation, I would appreciate that.

            • 3. Re: How to count customers with first sale within a certain week
              philmodjunk

                   The calculation field will only return a 1 if the SalesHistory record is the oldest such record for that customer. So searching for a record with a 1 in that field that also falls in your specified date range will find only "first time customer" records for that date range.

                   The relationship to saleshistory |samecustomer should not need to specify a sort order as the oldest record to be created should be the first record. The only except to that is if you sort the records in that table into a different order and then import them into a new copy of your file. If there is any chance that such could have happened then saleshistory |samecustomer should be sorted by date in ascending order.

                   

                        When you said  __pksaleshistoryid did you mean a sale id or the customer id in that table?

                   I meant that you need a field that uniquely identifies each field in the SalesHistory table. Ideally, this would be either an auto-entered serial number field or an auto-entered calculation field that auto-enters Get ( UUID ).