Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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;
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.