CODEWITHSUNDEEP
spotfire
Nischey D
Nischey D

Reputation: 199

Spotfire : formula for calculated column based on the other columns

I have an requirement to create an calculated column which calculates the difference of a value from previous row based on the value of other two columns.

FOR Example. Consider 4 columns as shown below. Col B and Col C has date values , Col A as some unique value and Col D will has some numerical values which will be used to calculate the required column, such that when Col B=Col C and corresponding value of Col D has to be taken and subtracted to Col D values until next time Col B=Col C occurs.

IMAGE of Table (PS: I couldnt copy whole table in copiable way as format was going to messy)

enter image description here

I need Output like below : ie when the date values are equal ,[COL B]=[COL C] (ie 20-12-2016 15:40), then I have to corresponding value of COL D will be considered and subtracted with 138 ,until next similar occurrence happens.Next occurrence is for value 12-01-2017 15:40 , then it should be subtracted with 161 and this continues and its dynamic.

PLEASE NOTE : For the values before [COL B]=[COL C], those corresponding values should be same as COL D or if the Value of COL C is NULL or BLank then also its should be as COL D as highlighted in Green.

OUTPUT REQUIREMENT (As in Yellow) enter image description here

I had used a code something as below in spotfire 

if([COL B] is Not NULL,If([COL B]=[COL C],First[COL D] - [COL D]),[COL D])

, however since the value to be subtracted will be dynamic I am not able to achieve it. Any help is appreciated

@Ksp585 : Please find below screenshot where it is calculating wrongly: After the green link next values should have been 751 (2638-3389) instead it has calculated as 3367 based on previous calculation (3389-22=3367) which is wrong:

enter image description here

Upvotes: 0

Views: 2895

Answers (1)

ksp585
ksp585

Reputation: 1770

@Nischey Anand- Please try the below solution.

Step 1: Add 'row_id' column

RowId()

Step 2: Insert calculation column with the below custom expression

If([Column C] is not null,Sum([Column D]) over ([row_id]) - first([Column D]) over ([Column C]),[Column D])

Here is the final output:

enter image description here

Hope this helps!

Answer updated based on additional details provided:

Add a calculated column with the below custom expression.

If(Sum(If([Column B]=[Column C],[Column D])) over ([Column C]) is not null,[Column D] - Sum(If([Column B]=[Column C],[Column D])) over ([Column C]),[Column D])

Here is the updated final output table:

enter image description here

Hope this solves your question!

Upvotes: 2

Related Questions