2 Replies Latest reply: Aug 21, 2012 3:57 PM by James Shenton RSS

    Advanced set analysis - sales since last meeting date

    James Shenton

      Hello

       

      Suppose I have a customer table, containing two fields: [Customer ID] and [Date of last meeting]. There is only one [Date of last meeting] per customer.

       

      A second table contains sales transactions, including: [Transaction ID] , [Customer ID] , [Date of Sale] and [Value of Sale].

       

      I need to produce a chart that shows, for each customer, the value of sales since the last time that customer was met.

       

      I can't quite get set analysis to work. It must be something like the below, but this doesn't work.

       

      sum ({1< [Customer ID] = P([Customer ID]) , [Date of Sale] >= [Date of last meeting] >} [Value of Sale] )

       

      Can this be done, and if so, how do I need to change the formula?

       

      Many thanks

      James