Sum over last 12 months based on date calculated column

[Matt Holland 3]

I have a set of data that has a well identifier, a date and a "flag" (1 or 0) in every row. The dates are irregular. For each row, I want to look back and sum the "flag" occurences over the last 90 days. If there was a row for every date for every well, I could use over/previous periods. See example data set below. I have calculated in excel the "Sum Last 90 Days" - this is what I want to be able to calculated as a calculated column in Spotfire. The sum will need to be per unique well (i.e. separate count for well 1 vs. well 2). I've attached the excel file also.

 

 

 

 

Well

_Date

_Date - 89d

Flag

Sum All Previous

Sum Last 90 Days

 

 

Well 1

3/13/2019

12/14/2018

1

1

1

 

 

Well 1

3/14/2019

12/15/2018

0

1

1

 

 

Well 1

4/24/2019

1/25/2019

1

2

2

 

 

Well 1

8/10/2019

5/13/2019

1

3

1

 

 

Well 1

8/17/2019

5/20/2019

1

4

2

 

 

Well 1

8/31/2019

6/3/2019

1

5

3

 

 

Well 1

9/26/2019

6/29/2019

1

6

4

 

 

Well 1

9/27/2019

6/30/2019

0

6

4

 

 

Well 1

9/28/2019

7/1/2019

0

6

4

 

 

Well 1

9/29/2019

7/2/2019

0

6

4

 

 

Well 1

9/30/2019

7/3/2019

0

6

4

 

 

Well 1

10/1/2019

7/4/2019

0

6

4

 

 

Well 1

10/2/2019

7/5/2019

0

6

4

 

 

Well 1

10/3/2019

7/6/2019

0

6

4

 

 

Well 1

10/4/2019

7/7/2019

0

6

4

 

 

Well 1

10/7/2019

7/10/2019

1

7

5

 

 

Well 1

10/8/2019

7/11/2019

0

7

5

 

 

Well 1

10/9/2019

7/12/2019

0

7

5

 

 

Well 1

10/10/2019

7/13/2019

0

7

5

 

 

Well 1

10/19/2019

7/22/2019

1

8

6

 

 

Well 1

10/23/2019

7/26/2019

1

9

7

 

 

Well 1

10/25/2019

7/28/2019

1

10

8

 

 

Well 1

10/28/2019

7/31/2019

1

11

9

 

 

Well 1

11/1/2019

8/4/2019

1

12

10

[Fabian Duerr]

As long as you date column is in the date format this is pretty straight forward. Here's the comparrison with the calculation on the y-axis. You can find this in the aggrgation methods of the y-axis:

Sum([Flag]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

 

Sum([Flag]) THEN Sum([Value]) OVER (LastPeriods(90,[Axis.X]))

For a calculated column you would just use:

Sum([Flag]) OVER LastPeriods(90,[Date])