topic Find the difference between values in weeks (script) in QlikView

Find the difference between values in weeks (script)

CNH_1978 — Wed, 25 Nov 2020 16:16:04 GMT

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 🙂

Re: Find the difference between values in weeks (script)

lironbaram — Tue, 18 Aug 2020 11:31:22 GMT

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;

Re: Find the difference between values in weeks (script)

CNH_1978 — Tue, 18 Aug 2020 11:37:57 GMT

fantastic, thanks very much 🙂