I don’t advertise it much, but I do fill requests for blog content. Last week, a Spotfire user contacted me via LinkedIn and asked the following question about a cumulative calculation…
Spotfire can definitely handle this situation. A single calculated column using the RANK function and an IF statement will take care of it. Just so we are all on the same page, let me break down the requirements of this calculation.
There are three distinct requirements in this request.
- Start production for each well at the same point in time (i.e. normalize the start date).
- Calculate the cumulative production for each well.
- Remove months of zero production.
Furthermore, here is an example of what the user wants to avoid…
Do you see those straight lines? Those are months of no production. The user does not want to see these straight lines. Straight lines make it hard to compare wells, so we are going to take those out.
To develop a solution to this problem, I created a quick and dirty dataset using only 2 wells. I’ve included this so you have column names for reference. Now, let’s get to the solution.
First, to make sure both wells begin production at the same time, we must create a calculated column to normalize the production date. Of course, there is more than one way to write this type of calculation. My preference is to use the Rank function.
Rank([Production Date],[Well Name])
The expression shown above is saying — Rank the Production Date for each Well Name. It is named “c.Normalized Time”. This is a good calculation if all we wanted to do was normalize the Production Date. Because we also want to remove months where production is zero, we need to include an IF statement like this…
If([Gas Prod]>0,Rank([Production Date],[FDOP]),null)
This expression returns a null value when there is no Gas Prod. Now that we have all the necessary calculations, let’s move on to the visualization. We will use the Cumulative Sum aggregation to calculate the cumulative gas prod. Note, I have a calculated column in the data table, but I don’t actually need it.
If we stopped here, the visualization would be not quite right. It would look like this one….see the (Empty) at the end of the visualization? Those are the nulls created by the IF statement. We can get rid of those by simply using the filters.
Here is the desired end result. And from my LinkedIn contact…
Content created with Spotfire 7.12.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!