Aggregation of data with limit to months

[arpitkharkia]

Hi guys,

PFA an excel file with sample data and desired output. I need to aggregate data by A_Month at A_ID level. The sum of A_Amount should be that of last 12 months.

For example for A_Month Jan-19, the amount should sum up to Feb -18 for each of the A_ID.

Thanks for the help!

[YoussefBelloum]

Hi,

Range functions exist for this kind of analysis, like this:

=RangeSum(Above(Amount_A,0,RowNo()))

PFA

[arpitkharkia]

Thanks for the reply!

The qvw looks good and the numbers are matching too, but is there anyway i can do this in the script(Backend) itself?

[YoussefBelloum · Accepted solution]

Yes, of course, on this document you will find what you're looking for, take a look at the "as-of-table":

Calculating rolling n-period totals, averages or other aggregations

[arpitkharkia]

The link you provided was really helpful. I am using asof tables but im still getting wrong numbers, aggregation of data is not happening properly and im not sure what the issue is!

i have the following code:

T:

LOAD date(Month_A,'MMM-YYYY') as Month_A,

    Amount_A,

    ID_A

FROM

(ooxml, embedded labels, table is Data);

NoConcatenate

T1:

Load *

    Resident T Order By ID_A,Month_A;

DROP Table T;

AsOfMonth:

load

Month_A,// as Month_AsOf,

//Amount_A as Amount_AsOf,

//ID_A as ID_AsOf,

date(addmonths(Month_A,1-iterno()),'MMM-YYYY') as Month_AsOf

Resident T1

while IterNo() <= 12;

// date(addmonths(Period,-iterno()),'YYYYMM') as Period1

right join load Month_A  Resident T1;

//EXIT SCRIPT;

NoConcatenate

F1:

    Load Month_AsOf as Month_A,

      Month_A as Month_AsOf

      Resident AsOfMonth;

Left Join  

F2:

    Load *

    Resident T1;

DROP Tables AsOfMonth,T1;

Test:

Load Month_AsOf,

    ID_A,

    Amount_A

    Resident F1;

    DROP Table F1;

//EXIT SCRIPT;

NoConcatenate

F3:

LOAD Month_AsOf,

    ID_A,

    sum(Amount_A) as TotalAmount_A

    Resident Test Group By Month_AsOf,ID_A;

DROP Table Test;  

[YoussefBelloum]

Hi,

is it ok for you ?