0 Replies Latest reply: Nov 17, 2015 4:10 AM by Lucía LP RSS

    Calculations in a table- previous values based on a condition

      Hi,

       

      I have this scenario:

       

      Amount 1Type 1Amount 2Type 2Amount 3
      1515R1555R               40
      1555R1584R29
      1584R43E43
      43E82E39
      1584R77R77
      77R114R37
      114R145R31



      I need to get the values for the "Amount 4" Column:

       

       

      Amount 1Type 1Amount 2Type 2Amount 3Amount 4
      1515R1555R                                         40                       0
      1555R1584R290
      1584R43E430
      43E82E390
      1584R77R7782
      77R114R370
      114R145R30

       

      The calculation for the Amount 4 column is:

       

      If the type of the Amount 2 is E, Amount 4 is 0.

       

      If the types of the Amount 2 and Amount 1 are R:

       

                          > If the type of the previous Amount 2 is R, the value of Amount 4 is 0

       

                          > If the type of the previous Amount 2 is E, I  have to sum all the previous "Amount 3" values with type E until I find one that                        is R.

                             For example, in this case Amount 1 = 1584, Amount 2 = 77, both values have a R-Type. The previous Amount 2 value                         (82)  is E-Type. So, I need to sum the Amount 3 values 39 and 43, the result is 82. Value 29 must not be added because                          it belongs to an Amount 2 value (1584) with a R-Type.

       

      How can I get the Amount 4 values with this logic in Qlikview? What functions are useful to solve this case? I am new to Qlikview and I am trying to find a solution.

       

      Thanks in advance.

       

      Best Regards,

       

      Lucía