I have a spotfire table with the following columns,
Well Date Oil_Rate_Forecast Oil_Rate_Actual Cum_Oil_Actual
a 1/1/2025 10 5 5
a 1/2/2025 10 5 10
a 1/3/2025 10 10 20
a 1/4/2025 20
a 1/5/2025 20
b 1/1/2025 10 5 5
b 1/2/2025 10 10 10
b 1/3/2025 10 5 20
b 1/4/2025 15
b 1/5/2025 15
I wish to create a new calculated column, called Final_Cum_Oil that should display Cum_Oil_Actual if data exists in the Cum_Oil_Actual column else, uses the Oil_Rate_Forecast column to extend the Cum_Oil Calculation. So, the final table should be:
Well Date Oil_Rate_Forecast Oil_Rate_Actual Cum_Oil_Actual Final_Cum_Oil a 1/1/2025 10 5 5 5 a 1/2/2025 10 5 10 10 a 1/3/2025 10 10 20 20 a 1/4/2025 20 40 a 1/5/2025 20 60 b 1/1/2025 10 5 5 5 b 1/2/2025 10 10 10 10 b 1/3/2025 10 5 20 20 b 1/4/2025 15 35 b 1/5/2025 15 50
How do I write the calculated column?
I tried using the expression
Sum(If([Cum_Oil_Actual] IS NOT NULL,[Cum_Oil_Actual],[Oil_Rate_Forecast])) OVER (Intersect(AllPrevious([Well]),[Date]))
but did not get desired results.
From what I understand of your goal, my proposed solution is:
Sum(SN([Oil_Rate_Actual],[Oil_Rate_Estimated])) over (Intersect([Well],AllPrevious([Date])))
where
SN([Oil_Rate_Actual],[Oil_Rate_Estimated])
uses Oil_Rate_Actual where defined and Oil_Rate_Estimated where not.
The sum is for each Well over all previous Dates.
This gives you a column with values: 5,10,20,40,60,5,15,20,35,50.