Advanced Aggregation (RangeMax/Above/Total/Aggr)

[#1]

Hi

I am pretty sure what I want to achieve can be done via the use of Aggr()/RangeMax()/Above/Total functions... Just not able to figure out how. Here is the data and the result I want

Time Stamp	ID 1	ID 2	ID 3	Value	Result
1/10/2017  2:00:00 PM	1	1	A1	100	100
1/10/2017  2:10:00 PM	1	1	A2	500	600
1/10/2017  2:15:00 PM	1	1	A2	800	900
1/10/2017  2:20:00 PM	1	1	A1	50	850
1/10/2017  2:20:00 PM	1	2	A3	100	950
1/10/2017  2:30:00 PM	1	1	A1	0	900
1/10/2017  2:30:00 PM	1	2	A4	50	950
1/10/2017  3:10:00 PM	1	2	A3	0	850
1/10/2017  3:30:00 PM	1	2	A5	1000	1850

The result is basically the SUM(Value) of the MAX(TimeStamp) row grouped by ID1, ID2 and ID3 where TimeStamp <= TimeStamp of current row.

Simply put, for each row, do the following:

1. Get all the previous rows (TimeStamp of those rows is less than or equal to the TimeStamp of current row) for each group of ID1, ID2, and ID3 over current selections

2. From this set of rows, get the latest row in each group based on TimeStamp (of those rows)

3. From this set of rows Sum the Value and display in result field

The data load is ordered by TimeStamp, ID1, ID2, ID3. It can be changed if needed

Example:

Row 1: There are no previous values, so the Result is the Value

Row 2: There are 2 groups now and there is only 1 row for each so the Result is 100+500

Row 3: There are still 2 groups, the group with A1 has only one row, so 100. The group with A2 has 2 rows of which the one with timestamp 1/10/2017  2:15:00 PM will be considered, so 800. Hence the sum is 900.

So on and so forth...

Please help!

Message was edited by: Prateek Somaiya Updated the table

[sunny_talwar]

Hahahaha yes, I already figured that part out

[jonathandienst]

Did you actually read the code to see what it does? Or did you get hung up on the field name? Maybe the name was not the best, so change it to whatever you want.

If you read the code, you would see that the field called MaxValue is the most recent value for the combination of ID1, ID2, ID3 on or before the Date value. Summing MaxValue by Date will give you what you want.