Calculate total footage drilled by zone

[Juan Carlos Gonzalez]

Hey guys!

Does anybody know how can i calculate the total distance drilled and the percentage drilled in each zone.?
I have tried to use RowID, I have tried create a new Rank column, Tried everything I could think of, but I can't get reference the immediate next row to identify when the data changes zone and from there take the max and min value before the zone change happens. I don't know if i have the wrong approach and I should try a function or something.

Could anybody please give me a hand? 

 

[Gaia Paolini]

can you look at using something like sum(distance) over [zone]? This will calculate the sum within each zone. You can add multiple slicing columns (normally add an intersect(..) around the multiple columns

[Juan Carlos Gonzalez]

@Gaia Paolini thank you for your response. I have tried that, but the result is the sum of each row and I just need to perform a subtraction: For each segment a zone is drilled Last Value - First Value. It means that for every time the well enters a zone (repeated or not) I should perform the subtraction and then sum all of the segments. Like in the picture I would need to calculate segment A and C separately and the sum them up.

[Gaia Paolini]

I am not a domain expert and that picture looks quite advanced. Can you describe a simple use case?

[Juan Carlos Gonzalez]

@Gaia Paolini I have a mock dataset (attached) where I have the values that I am supposed to pull out.

 

Right now I tried to pull the values with this expression: 
CASE  
WHEN [Wellname]='Well1' THEN 
Last([MD]) OVER (Intersect([Zone],[Wellname])) - First([MD]) OVER (Intersect([Zone],[Wellname]))
END

Having the wrong results (as shown in the screenshot bellow) 

The actual values should be

 

Well1 DataSet.xls Well1 DataSet - Well1 DataSet.dxp

[Gaia Paolini]

Thank you, I think I can see what you are calculating, but I don't understand what you want to calculate.
Right now you are calculating for each Well and Zone, the difference between the last and the first value of MD.

I can see that each Zone can be repeated. 

But, for instance, where does 13420 for TI1 come from? How would you calculate it, were it not in Spotfire but on a piece of paper?

[Gaia Paolini]

OK I can see the calculations on Excel.  So I understand that you want to calculate the difference between the last and first each time the Zone re-occurs, then sum it over the zones.
But I would have put a zero, not a 13170 on T1, as here there is only one row and no difference.

So I would say the amount for T1 you want is 10+70+40+30+0+50+50, not  10+70+40+30+13170+50+50.

Can you confirm either way?

[Gaia Paolini]

Pending your reply, this is what worked for me (but it generates 250 not 13420 for TI1).

You want to create a group index so that when a Zone re-occurs, you don't treat it like the same Zone.

Create a [rowID] calculated column defined as

RowId()

Create a [STEP] column like this:

SN(Lag(Concatenate([Wellname],[Zone]))!=Concatenate([Wellname],[Zone]),True)

This will be True when it is the first element, or when a change happens from the previous line, including change of well name.
Then the [GROUP_INDEX] is:

sum(Integer([STEP])) over (AllPrevious([rowID]))

You can avoid defining [STEP] separately once you are happy it works.

Then the [DELTA] (your Footage) for each Zone taking repeats into account is:

 Last([MD]) OVER (Intersect([Zone],[GROUP_INDEX],[Wellname])) - First([MD]) OVER (Intersect([Zone],[GROUP_INDEX],[Wellname]))

However, now you have [DELTA] defined over each row. To make display simple, you could define an associated column [DELTA2] that is only defined at
every step change:
 

case  when [STEP]=True then [DELTA] end

Now it is easier to use it in a cross table. Using columns [Zone] and [Wellname] and Sum([DELTA2]) as cell values.