0 Replies Latest reply: Apr 23, 2014 12:14 PM by João Nuno Duarte RSS

    Expressions based on different dimensions

    João Nuno Duarte

      Hello.

       

      I have a table with the following structure:

       

      timestampdata1data2data1*data2
      ts1a1a*1
      ts2b1b*1
      ts3c1c*1
      ts4d2d*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 AScenario B
      time_intervalts4-ts3ts2-ts1 (=ts4-ts3)
      sum(data1)c+dc+d
      sum(data2)1+21+1
      sum(data1*data2)c*1+d*2c*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