Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi,
Range functions exist for this kind of analysis, like this:
=RangeSum(Above(Amount_A,0,RowNo()))
PFA
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?
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
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;
Hi,
is it ok for you ?