Aggregate daily data as month end figures

[senarath]

Hi,

I have a existing data set for a month as below

Date                         Amount

01-Jan-2016               10

02-Jan-2016               13

03-Jan-2016               11

04-Jan-2016               12

05-Jan-2016               16

...............                  .....

31-Jan-2016               20

My requirement is that I want to get the aggregated amount as of month end date. because I have to link these data to another data model which has figures as at month end date only.

Required to arrive at the total for the month with the month end date in the load script

Date                    Amount

31-Jan-2016          356

Thanks

Senarath

[Gysbert_Wassenaar · Accepted solution]

Table1:

LOAD

     Date,

     MonthEnd(Date) as MonthEnd

     Amount

FROM

     ...source...

;

Table2:

LOAD

     MonthEnd,

     sum(Amount) as MonthTotal

RESIDENT

     Table1

GROUP BY

     MonthEnd

     ;

Or perhaps simpler if your source data is already sorted chronologically:

LOAD

     Date,

     Amount,

     If(MonthStart(Date) = MonthStart(Previous(Date)), RangeSum(Amount, Peek(RollingMonthTotal)), Amount) As RollingMonthTotal

FROM

     ...source...

     ;

[senarath]

Many thanks gysbert ...!!!! 🙂