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.
