Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
as a part o spring-cleaning I want to refresh some old-coded reports. Using the below master calendar and source data how would you get a chart showing outstanding amounts as per the below results:
//MasterCalendar
LET vDateMin = Num(MakeDate(2012,3,1));
LET vDateMax = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarData Resident TempCalendar ORDER BY TempDate Desc;
DROP TABLE TempCalendar;
//SourceData
LOAD * INLINE [
InvNo, DateOfInv, DateOfPayment, AmountOfInv, AmountPaid
IN1, 2012-03-19, 2012-03-20, 10, 8
IN2, 2012-03-22, 2012-03-23, 12, 12
IN3, 2012-03-23, '', 6, ''];
//Results
CalendarDate, OutstandingAmount
2012-03-19, 10
2012-03-20, 2
2012-03-21, 2
2012-03-22, 14
2012-03-23, 2
2012-03-24, 8
Regards,
Przemek
Just to refresh the thread...