Calculating days between dates and days in month for first value to avoid blank rows

[Tor Meling]

The problem. SPOTFIRE INSISTS ON INSERTING A BLANK CELL IN MY CALCULATED COLUMN.

Please help! I assume that it is my inexperience that makes me unable to make this work as I cant imagine that something as basic as this is not possible in spotfire.

 

My dataset looks like this:

 

Well ID Date c.Days0 c.Days1

Well1 date calc calc

: date calc calc

Well1 date calc calc

Well2 5/31/2019 31

Well2 6/30/2019 30

Well2 7/31/2019 31 31

Well2 8/31/2019 31 31

Well2 9/30/2019 30 31

Well2 10/31/2019 31 31

Well2 11/30/2019 30 31

Well2 12/31/2019 31 31

Well2 1/31/2020 31 31

Well2 2/29/2020 29 31

Well2 3/31/2020 31 31

Well2 4/30/2020 30 31

Well2 5/31/2020 31 31

Well2 12/31/2020 245 31

Well2 12/31/2021 365 31

Well2 12/31/2022 365 31

: : : :

: : : :

 

I want to calculate a column that has the days since previous period, and if this is the first record for that well, to find the number of days in the month. I can do it by inserting 2 extra columns, but I have a very large dataset, so I do not want to add any more columns than absolutely necessary.

 

First it is easy to calculate days from 1 row to the next, but for the first row for Well 2 there is no prior value, so the first cell will naturally be blank, so the output above is using this formula (c.Days0 column)

DateDiff("dd",Min([PERIOD]) OVER (Previous([PERIOD])),[PERIOD])

 

So I tried to fix the initial hole in the data by adding an if statement, but instead of fixing the problem it shifted the blank one line down

 

if(DateDiff("dd",Min([PERIOD]) OVER (Previous([PERIOD])),[PERIOD]) is not null,DateDiff("dd",Min([PERIOD]) OVER (Previous([PERIOD])),[PERIOD]),DateDiff("day",DateAdd("day",(-DayOfMonth([PERIOD])) + 1,Date([PERIOD])),DateAdd("month",1,DateAdd("day",(-DayOfMonth([PERIOD])) + 1,Date([PERIOD])))))

 

Is there a way to put in a formula that generates a value for all occurrences

[Shashank Gurnalkar]

I don't have the solution without extra calculated column, but with just one extra column you can achieve this use case (just FYI as you mentioned two extracolumns).

Apart from that, the behavior that you are seeing with the addition of If statement is as designed.

When using OVER function along with any condition (If statement or Case statement), it will only consider the subset for which the condition evaluates to TRUE. The reason why the NULL value is shifted to the second row is because 'is Not Null' subset starts from the second row till the end for that particular well.