Use Previous Row Calculated value in Current Row

[Balthazar]

I want to know if we can calculate the value of C using Cognos Report Studio 10.2.2.
Note: I can get previous value of B using running-difference, but don't know if it is possible to get previous value of a calculated data item.
Thanks in Advance.


A       B       C (Calculated Value)
----------------------------------------------
1     0.01     100     --->     Constant Value
2     0.01     101     --->     C1 * (1 + B2)
3     0        101     --->     C2 * (1 + B3)
4     0.02     103.02  --->     C3 * (1 + B4)

[tjohnson3050]

Create two queries, join them on complex expression:

Q1.A = Q2.A+1

The result can be a query set that allows you go compare one row with a previous row in a calculation.

[Balthazar]

Thanks for responding, tjohnson3050. Using join, I can get the previous value of column B whereas Column C is a nested calculation. Is there a way to perform this?

Thanks.

[tjohnson3050]

You can do this using the join method.  Query 1 and 2 both have your columns A and B.  Query 3 will be the target of Query 1 and 2.  Query 3 will have the calculated column C.

[Balthazar]

The value of C is calculated using more than the current value and previous row value (as it is nested). In the example below, How do I give the logic for Row 4?

Query1 is A,B and Query 2 is PrevA, PrevB. Query 3 is joined on (PrevA = A + 1)

A     B        PrevA    PrevB    C       Logic
1     0.01     Null     Null     100     if (A = 1) then (100)
2     0.01     2        0.01     101     else if (A = 2) then (100 * (1 + B2))
3     0        3        0.01     101     else if (A = 3) then ((100 * (1 + PrevB3)) * (1 + B3))
4     0.02     4        0                else (???)

Thanks