When you are saying 'for every possible day in a given date range', how do you create the date range? Do you work with a master calendar?
One way to solve it would be to create FromDate and ToDate in the script giving the date range that the quantity is valid, and then use IntervalMatch together with a master calendar.
If you don't want to use a master calendar (which I would suggest to use anyway), could you describe how you want to display / create the date range?
I do have a master calendar.
I was thinking about using IntervalMatch, but that would give me a lot of data redundancy and would probably influence performance.
I could use seperate calendar to create the date range. I am not sure if that's the right approach and how should the expressions look like in such case.
Many thanks, Przemek
if you want to keep the calendar and the Quantity tables separated you could use something like this:
LET vDateMin = Num(MakeDate(2011,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
Date($(vDateMin) + RowNo() - 1) AS CalendarDate
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
LOAD * INLINE [
ID, Date, Quantity
1, '01/12/2011', 11
1, '04/12/2011', 22
1, '08/12/2011', 55
1, '10/12/2011', 33
LOAD ID, Date as DateFrom, alt(previous(Date)-1,$(vDateMax)) as DateTo, Quantity resident Data order by Date desc;
drop table Data;
And then a simple chart with dimension CalendarDate and as expression:
=sum(if(CalendarDate<=DateTo and CalendarDate >= DateFrom, Quantity))
This will probably be not very performant and I would personally go for doing it in the script.
You could do it with an intervalmatch or by filling in all missing values in your Quantity table.
Something similar is also just being discussed here: