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

# Calculations in a table- previous values based on a condition

Hi,

I have this scenario:

 Amount 1 Type 1 Amount 2 Type 2 Amount 3 1515 R 1555 R 40 1555 R 1584 R 29 1584 R 43 E 43 43 E 82 E 39 1584 R 77 R 77 77 R 114 R 37 114 R 145 R 31

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

 Amount 1 Type 1 Amount 2 Type 2 Amount 3 Amount 4 1515 R 1555 R 40 0 1555 R 1584 R 29 0 1584 R 43 E 43 0 43 E 82 E 39 0 1584 R 77 R 77 82 77 R 114 R 37 0 114 R 145 R 3 0

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.