Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

0 Replies