Business Intelligence Tools

How to Remove Months of Zero Production from a Cumulative Calculation

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…

        Is it possible to create cumulative production plots that automatically exclude months that have zero production?  Oftentimes when we make cum plots manually.  We completely delete months that have zero production. Wells have shut-ins for various reasons, and if included in the dataset, these time periods show a flat line on the cum plot. This makes it difficult to visually compare wells against each other, because some have downtime, and some don’t. So we want to disregard zero months completely, and not count them on the x or y axis. LinkedIn Contact

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.

Requirements

There are three distinct requirements in this request.

  1. Start production for each well at the same point in time (i.e. normalize the start date).
  2. Calculate the cumulative production for each well.
  3. 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.

Data Set

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.

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…

Awesome, Julie!  Thanks for taking my request.  Looks like it wasn’t too complicated.  This should help a lot of “basic” users like myself.

 

 

Spotfire Version

Content created with Spotfire 7.12.

Leave a Reply

Your email address will not be published. Required fields are marked *