Calculation for month number in time series data

Question

The data I am working with is oil and gas production data. The production table uniquely identifies each well and contains a time series of production values. I want to be able to calculate a column that contains the month number occurrence of production for every well in the production table. This needs to be a calculation, so I can graph the production for various wells based on the production month, not the calendar month. (I want to compare well performance across wells over the life of wells.) Also note that there could be gaps in the production data so you can't depend on having twelve months of sequential production for each well.

I tried using the answer in this postRankValues but the calculation would never finish. I have over 4 million rows of production data.

In the table shown below, the values shown in ProdMonth is what I need to calculate based on their time occurrence shown in ProdDate. This needs to be performed as a row calculation for each unique WellId

Thanks.


WellID  ProdDate    ProdMonth
1       12/1/2011      1
1       1/1/2012       2
1       2/1/2012       3
1       3/1/2012       4
…         …            …
1       11/1/2012      12
2       3/1/2014       1
2       4/1/2014       2
2       5/1/2014       3
2       6/1/2014       4
2       7/1/2014       5
…         …            …
2       2/1/2014       12

Lukasz P. Lukasz P. · Accepted Answer · 2015-09-21T23:16:15

I would create a new date table that has a row for each day (the granularity of your data). I would then add to that table the ProdMonth column. This will ensure you have dates for all days (even if there are gaps in the well reporting data). Then you can use a relationship between the well production data and the Date table on the ProdDate field. Then if you pull in the ProdMonth from the date table, you'll have a list of all of the ProdMonths (hint: you may need to select 'show values with no data' on the field right click menu in the fields well). Then if you add to the same visualization WellID you should be able to see which wells were active in which ProdMonth. If WellID is a number, you might need do use the 'do not summarize' feature on the WellID to get the result you desire.

Calculation for month number in time series data

2 Answers