Find the difference between values in weeks (script)

[CNH_1978]

Hi,

I have some data that i am loading in,

Name

Dim1

dim2

Date

Value

i want to add a field in the script that will show me the difference in values for each name by date. 

e.g.

Name	Dim1	Dim2	Dim3	Dim4	25/06/20	26/06/20	27/06/20
Value1	199,448	199	0.00	0	748	749	749
Value2	175,729	176	0.00	0	937	938	938
Value3	121,676	122	0.00	0	372	373	374

so the new field will contain the following (if 25/06/20 was the first date in the data)

Name	25/06/20	26/06/20	27/06/20
Value1	748	1	0
Value2	937	1	0
Value3	372	1	1

I know i would have to use the peek function but i'm a bit lost. 

Can anyone help please?

I have attached some files. 

Thanks 🙂

[lironbaram · Accepted solution]

hi 

this script should work for you 

Data_TMP:
CrossTable(Date, Data, 5)
LOAD *
FROM
[C:\Users\Liron\Downloads\Test_Data.xlsx]
(ooxml, embedded labels, table is Data)
where RowNo()<=29;





Data:
Load 
	Name,
	Dim1,
	Dim2,
	Dim3,
	Dim4,
	Date,
	Data,
	if(Previous(Name)=Name,Data-previous(Data),Data) as DateDiff
Resident Data_TMP
order by Name , Date;

drop table Data_TMP;

[CNH_1978]

fantastic, thanks very much 🙂