Subtraction/difference applied on same column.

[poojahashmap]

Sometimes you might want to get difference between consecutive values from one column. Approach:- In my case I had to subtract values in [r1]. Logic was : Formula: r1_calc = first earliest date – second earliest date

Explanation: 1/18/18 - E ; D-E 1/17/17 - D ; C-D 6/1/16 - C ; B-C 3/1/16 - B ; A-B 1/1/16 - A ;

1. Give rowID to the column you want to perform operation on. Rank([insp_date],[joint_no],[tag],[location],[monitoring_point])

Tip: Here I have used rank because I had to rank the earliest [insp_date]

2. Use formula which is similar to (-[r1]) + Max([r1]) over (Intersect(Previous([RowNum]),[tag],[joint_no],[location],[monitoring_point]))

Shows the result- r1_calc_new is the desired new column.

[poojahashmap]

So basically we found difference between consecutive [r1] values corresponding to particular [tag],[joint_no],[monitoring_pont], [insp_date].