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
