Price variations by aggregation

[andreip21]

Hello all,

I have the following data: 6 products from 2 brands and price during 6 days period:

Brand	Product ID	1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec
A	1	20	20	-	20	30	30
A	2	-	-	-	-	45	45
A	3	75	60	60	80	80	80
A	4	10	10	10	-	-	-
B	5	100	100	100	100	90	90
B	6	35	-	40	30	30	30

i will have differences day by day (2-Dec vs 1-Dec, 3-Dec vs 2-Dec):

when the product has no price in 2 consecutive days, the differences will be 0 (e.g. product ID 6 on 2-Dec vs 1-Dec)

Brand	Product ID	1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec
A	1		0	0	0	10	0
A	2		0	0	0	0	0
A	3		-15	0	20	0	0
A	4		0	0	0	0	0
B	5		0	0	0	-10	0
B	6		0	0	-10	0	0

and then i want to calculate for the 2 brands the price variations between days:

Brand		1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec
A			-14%	0%	33%	10%	0%
B			0%	0%	-7%	-8%	0%

the formula for the last table will be:

By Brand: Sum of differences 2-Dec (from second table)/ Sum of prices 1-Dec (from first table)

but only for products that have positive values on 2 consecutive days (1-Dec and 2-Dec)

I attached the Excel file with an easy understand of what i want.

How to do this in Qlik?

Thank you,

Andrei

[andreip21]

any help, guys?