YTD to Month in loading script

[antosraf]

HI All

Can you give some ideas how to split YTD data to month

I have YTD data so feb is sum of Jan and Feb and so on, I need some loop to recalculate this and have data per month.

orginal data

load * inline
[
period,Product,Value
201801,A,100
201801,B,110
201802,A,220
201802,C,100
201803,A,200
201803,B,200
201803,C,120
]

I need

period	Product	Value
201801	A	100
201801	B	110
201802	A	120
201802	C	100
201803	A	-20
201803	B	90
201803	C	20

BR

Rafal

[Vegar · Accepted solution]

I got this tablebox from the script below.

RawData:
load * inline
[
period,Product,Value
201801,A,100
201801,B,110
201802,A,220
201802,C,100
201803,A,200
201803,B,200
201803,C,120
] 
;
FinalData:
LOAD 
  period, 
  Product, 
  Value,
  if(peek('Product')= Product, Value - Peek('Value'), Value ) as MonthlyValue
Resident RawData
ORDER BY Product, period
;
DROP Table RawData;

[antosraf]

Thanks, this is I was looking for