Advanced set analysis - sales since last meeting date

[#1]

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

[swuehl · Accepted solution]

Set analysis is evaluated once per chart, so it won't look at the dimension values (that's what you probably intend by using Date of sale >= Date of last meeting).

So I don't think set analysis is the way to go here.

You should be able to use a conditional embedded in the sum to check if the date of sales is after the date of last meeting:

=sum(if([Date of Sale] >= [Date of last meeting], [Value of Sale]))

Another approach would be to use an IntervalMatch between your two tables (creating a second field for the interval end in your customer table with date of today). Check the Intervalmatch Load prefix if you are interested in that.

Hope this helps,

Stefan

[#3]

Thanks Stefan. Worked exactly how I wanted it.

James