How to compare two fields using set analysis?

[#1]

Hello,

I want to compare two fields in my set analysis expression.

Assume I have a table with sales data, and it contains information such as:

Date	AgentID	SalesAmount
1/1/2016	1	1000
1/2/2016	1	2000
1/3/2016	1	1200
1/3/2016	2	1400
1/4/2016	2	900

I also have another table with data about my sales agents:

AgentID	AgentName	Team	AgentTeamStartDate	AgentTeamEndDate
1	John Doe	Team 1	1/1/2016	1/2/2016
1	John Doe	Team 2	1/3/2016	12/31/9999
2	Mary Jane	Team 1	1/1/2016	12/31/9999

Agents can belong to multiple teams over time and their duration of time on a team will be reflected by the AgentTeamStartDate and AgentTeamEndDate values.

Assume that I have a list box for Team, as well as for Date, and an object with a set expression that is summing the Sales Amount (as well as filtering on non-relevant fields/values).

Suppose I filter down to date range 1/1/2016 - 1/3/2016 in my Date listbox, and for Team 1 in my Team listbox. Currently I would get all sales records from 1/1/2016 - 1/3/2016, not only those that were made by agents who belonged to Team 1 at the time. This is because I am joining my Sales table to my Agents table on AgentID, rather than any date field, and doesn't take into consideration the AgentTeamStartDate and AgentTeamendDate field values.

How can I write an expression so that when a user filters on a specific Team and Date range, Sales Amount is summed for only the duration that the date selection falls within?

I am thinking it would be something like this but I can't figure out the exact syntax:

Sum({<

Date = {">=AgentTeamStartDate"}

,Date = {"<=AgentTeamEndDate"}

>} SalesAmount)

Additional nuances of my application require me to use set expressions, rather than an IF statement to sum the sales amounts, and also do not allow me to change the structure of the data model.

[swuehl]

I would try to resolve this in the data model.

What your coping with is a so called slowly changing dimensions.

Please have a look at:

Slowly Changing Dimensions

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

Creating Reference Dates for Intervals

edit: So one possible solution might be using IntervalMatch (added after loading the two tables in your script):

JOIN (YourSalesAgentTable)

INTERVALMATCH (Date, AgentID)

LOAD AgentTeamStartDate, AgentTeamEndDate, AgentID

RESIDENT YourSalesAgentTable;

[#3]

You can try this formula:

sum({$<AgentTeamStartDate={"<=$(=min(Date))"},AgentTeamEndDate={">=$(=max(Date))"}>} SalesAmount)

Though changing the data model , advised by swuehl, seems to be much better solution.