Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Stefan. Worked exactly how I wanted it.
James