Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
any help, guys?