Hello.
I have a table with the following structure:
| timestamp | data1 | data2 | data1*data2 |
|---|
| ts1 | a | 1 | a*1 |
| ts2 | b | 1 | b*1 |
| ts3 | c | 1 | c*1 |
| ts4 | d | 2 | d*2 |
where the first column (dimension) has evenly distributed timestamp values. The aim is to compare two scenarios.
Let scenario A be composed by the data in rows ts3 and ts4.
Then, scenario B would consist in relocating data1 associated with ts3 and ts4 to timestamps ts1 and ts2. Therefore:
| Scenario A | Scenario B |
|---|
| time_interval | ts4-ts3 | ts2-ts1 (=ts4-ts3) |
| sum(data1) | c+d | c+d |
| sum(data2) | 1+2 | 1+1 |
| sum(data1*data2) | c*1+d*2 | c*1+d*1 |
I have an input box where the user specifies the new time start for scenario B as a variable. Then, I am using set analysis to associate data1 from scenario A to the corresponding timestamps in scenario B and everything is working fine.
The problem comes with data2 and, consequently, with data1*data2, since data2 values may not be equal from scenario A to scenario B. (In the example above, data1=c keeps being associated with data2=1, but for data1=d, data2 assumes value "1" and no longer "2").
I have tried to overcome this issue by using variables/set analysis, but I have not succeeded yet.
Have you any idea for a possible solution? Is there any function (like match/index/...) that can be useful?
Thank you & Best regards,
João
