subtract previous rows based on condition

[Nikki Bueno de Mesquita]

Hi,

I have some data for which I need to subtract the previous row of data based on two conditions. The Data looks like this and I am looking to calculate column P

 

 

 

Area

Test Number

Rate

A

T

P

 

 

A

1

1

67.5

266.8

3.61

 

 

A

1

2

92.4

261.3

4.53

 

 

A

1

3

115.1

253.6

2.95

 

 

A

2

1

41.2

269.4

4.57

 

 

A

2

2

71.2

265.2

7.14

 

 

A

2

3

101

258.6

4.52

 

 

A

2

4

128.5

250.3

3.31

 

 

A

3

1

64.9

260.7

3.79

 

 

A

3

2

71.4

259.2

4.33

 

 

A

3

3

97.9

253.5

4.65

 

 

A

3

4

133.8

242.5

3.26

 

 

B

1

1

36

260.2

5.89

 

 

B

1

2

72.6

257

11.44

 

 

B

1

3

127.3

244.7

4.45

 

 

B

2

1

45.4

259.5

4.90

 

 

B

2

2

68.2

256.1

6.71

 

 

B

2

3

102.9

249.3

5.10

 

 

B

2

4

128.2

242.6

3.78

 

 

B

3

1

43.8

257.8

5.94

 

 

B

3

2

66.4

254.5

6.85

 

 

B

3

3

103.2

247.8

5.49

 

 

 

So some test can have 3 rates and others 4 rates. There can be more than 3 tests per Area.What I need to do is the following:

If the test number for row and row-1 are the same then P = (A - A(row-1)) / (T(row-1) - T).

-->soif we have 3 rates then applyfor rate 2 and 3

-->soif we have 4rates then applyfor rate 2, 3 and 4

This means that the row for Rate 1 will stay empty for that one I want to calculate P =(A(highest Rate) - A) / (T - T(highest Rate))

Thanks in advance

[Khushboo Rabadia]

You can create four calculated columns i.e. Aprev, Ahigh, Tprev and Thigh. Then use them to calculate expression for P

You can use over function in your calculated column expression.

https://docs.tibco.com/pub/sfire-analyst/10.3.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_over_in_calculated_columns.htm

Previous keyword in over function can help to get previous row value like

Aprev = First([A]) OVER (Intersect([Area],[Test Number],Previous([Rate])))

 

Where A value of previous Rate will be extracted per Area per Test Number

Similary, you can calculate Tprev

For getting the highest rate value, you can extract which is the Max Rate in each Area, each TestNumber then extract A value where [Rate]=this max rate like

First(case when [Rate]=Max([Rate]) OVER (Intersect([Area],[Test Number])) then [A] end) OVER (Intersect([Area],[Test Number]))Similarly, you can calculate Thigh

Now, as you have all the required columns, you can create your final expression for P

case when [Rate]=1 then ([AHigh] - [A]) / ([T] - [THigh]) else ([A] - [APrev]) / ([TPrev] - [T]) end