Suppose you have these line-item and snapshot/lookup tables that you want to relate:
Contracts
ID,Type,Date,Amt
1,PO,1/20,$100
1,PO,2/20,$200
1,PO,2/23,$300
2,PO,2/10,$1000
2,PO,2/15,$2000
2,PO,2/20,$3000
Inventories
ID,Date,Inv
1,1/1,100
1,2/1,200
1,3/1,300
2,1/1,400
2,2/1,600
2,3/1,800
And you are filtering the date range 2/1 to 4/1 inclusive, aggregating the Contracts.amt as SUM(amt), and aggregating the Inventories.Inv as Average(Inv). (Actually, it does not matter how you roll up inventory because you are getting a single single row for each ID.)
Suppose the desired result is a table like this:
ID,Sum(Amt),Avg(Inv)
1, $500, 200
2, $6000, 600
The idea is that the first row of Contracts where ID=1 is dropped because it is before 2/1 and the Inventories.Inv value is picked from the minimum matching value in the date range.
If we were expressing this in SQL, we might go with this:
SELECT ID, SUM(a.Amt), AVG(b.Inv)
FROM Contracts a
JOIN Inventories b on a.ID = b.ID
WHERE MONTH(b.DATE) ....
AND MONTH(a.Date) BETWEEN 2 and 4
GROUP BY a.ID
And the problem is how to get the correct WHERE MONTH(b.Date) filter.
It is tempting to think that this would work:
WHERE MONTH(b.Date) = MIN(MONTH(a.Date))
The problem is that the rows for Contracts could just as easily be this:
ID,Type,Date,Amt
1,PO,1/20,$100
1,PO,3/20,$200
1,PO,3/23,$300
Then this query would pick up the inventory value for 3/1 instead of 2/1. So the outcome is that the date selection for Inventories must use the parameter for the date range, not some value found in the Contracts data.
If we translate this to Sisense, how do we select our data? We can’t filter on the Inventories.Date and get it to reference the filter range, as far as I know. And we cannot construct a lookup table that does the right thing either.
So this is where I am stuck on this: I can’t see how to construct an as-of date like this in Sisense.