1 Reply Latest reply: Apr 2, 2012 3:02 AM by Przemysław Wojda RSS

    Sum of outstanding amount on master calendar date

    Przemysław Wojda

      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