Hi,
I have a table like this:
RV:
LOAD * INLINE [
DIM1, DIM2 , Week, SOH, TD
A, A, 201645, 10, 5
A, A, 201646, 5, 1
A, A, 201647, 4, 3
A, A, 201648, 2, 3
B, A, 201645, 20, 5
B, A, 201646, 15, 1
B, A, 201647, 14, 3
B, A, 201648, 12, 3
];
What I need is a final table with this structure:
DIM1, DIM2 , Week, SOH, TD, Week1, Week2, Week3, Week4
A, A, 201645, 10, 5, 5, 4, 1
A, A, 201646, 5, 1, 4, 1, -2
A, A, 201647, 4, 3, 1, -2, -2
A, A, 201648, 2, 3, 1, 1, 1
B, A, 201645, 20, 5, 15, 14, 11
B, A, 201646, 15, 1, 14, 11, 8
B, A, 201647, 14, 3, 11, 8, 8
B, A, 201648, 12, 3, 9, 9, 9
For the group of dim1 and dim2, look in each week, calculate:
Week1: sum(SOH) - rangesum(after(sum(TD),1,1))
Week1: sum(SOH) - rangesum(after(sum(TD),1,2))
Week1: sum(SOH) - rangesum(after(sum(TD),1,3))
I can do this calculation in a diagram measure formular,
but that cost too must time.
I would like to calculate it in the load script.