Nikki Bueno de Mesquita Posted September 25, 2019 Share Posted September 25, 2019 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 Link to comment Share on other sites More sharing options...

Khushboo Rabadia Posted September 27, 2019 Share Posted September 27, 2019 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 Link to comment Share on other sites More sharing options...

## Recommended Posts

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account## Sign in

Already have an account? Sign in here.

Sign In Now