topic Re: Difference by Date and Product in QlikView

Difference by Date and Product

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

Hello,

I need to calculate the difference between dates and products.

here i attach you some data.

Date	Product	Value	Difference
05/05/2017	1	34	0
05/05/2017	2	23	0
06/05/2017	1	2	-32
06/05/2017	2	12	-11

From what i have read, it can be done with the peek function, however i always see examples with one dimension.

¿Could you help me?

Re: Difference by Date and Product

sunny_talwar — Wed, 14 Jun 2017 11:21:33 GMT

You need this in the script or on the front end of the application?

Re: Difference by Date and Product

vinieme12 — Wed, 14 Jun 2017 11:32:28 GMT

try below if you need to do this in script

temp:

load * inline [

Date,Product,Value,Difference

05/05/2017,1,34,0

05/05/2017,2,23,0

06/05/2017,1,2,-32

06/05/2017,2,12,-11

];

NoConcatenate

NEW:

load Product,Date,Value,if(Product=Previous(Product),Value-Peek(Value),0) as Difference resident temp

order by Product,Date;

drop table temp;

Re: Difference by Date and Product

Anonymous — Wed, 14 Jun 2017 11:58:10 GMT

Thank you Vineeth it makes the job!!

However i would like to understand how it is doing.

¿if instead of two dimensions there are 3?

I have added an employee and the difference is not well calculated. ¿how can i make it?

Re: Difference by Date and Product

sunny_talwar — Wed, 14 Jun 2017 12:33:26 GMT

Not sure what the output needs to look like, but try this:

temp:

load * inline [

Date,Product,Employee,Value

05/05/2017,1,1,34

05/05/2017,2,2,23

06/05/2017,1,1,2

06/05/2017,2,3,12

];

NoConcatenate

NEW:

load Product,

Date,

Value,

Employee,

if(Product=Previous(Product) and Employee = Previous(Employee), Value-Peek(Value),0) as Difference

resident temp

order by Product, Employee, Date;

drop Tables temp;

Basically Date should be the last sort for you and if you want to only subtract for the same Employee, then add that condition in your if statement as well as in the order by clause

Re: Difference by Date and Product

Anonymous — Wed, 14 Jun 2017 12:40:33 GMT

¡thank you, now i understand it!

Re: Difference by Date and Product

Anonymous — Wed, 14 Jun 2017 12:46:27 GMT

If it´s not a big problem, i would like to know how can it be done the difference only applying it to the same year.

I am asking to know all the possibilities about this function

Re: Difference by Date and Product

sunny_talwar — Wed, 14 Jun 2017 12:50:24 GMT

Try this

temp:

load * inline [

Year,Date,Product,Employee,Value

2017,05/05/2017,1,1,34

2017,05/05/2017,2,2,23

2017,06/05/2017,1,1,2

2017,06/05/2017,2,2,27

2017,06/05/2017,2,3,12

2016,05/05/2016,1,1,34

2016,05/05/2016,2,2,23

2016,06/05/2016,1,1,2

2016,06/05/2016,2,2,27

2016,06/05/2016,2,3,12

];

NoConcatenate

NEW:

load Product,

Date,

Year,

Value,

Employee,

if(Product=Previous(Product) and Employee = Previous(Employee) and Year = Previous(Year), Value-Peek(Value),0) as Difference

resident temp

order by Product, Employee, Year, Date;

drop Tables temp;