Juan Carlos Gonzalez Posted June 20 Share Posted June 20 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? Link to comment Share on other sites More sharing options...
Gaia Paolini Posted June 21 Share Posted June 21 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 Link to comment Share on other sites More sharing options...
Juan Carlos Gonzalez Posted June 21 Author Share Posted June 21 @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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted June 21 Share Posted June 21 I am not a domain expert and that picture looks quite advanced. Can you describe a simple use case? Link to comment Share on other sites More sharing options...
Juan Carlos Gonzalez Posted June 28 Author Share Posted June 28 @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 Link to comment Share on other sites More sharing options...
Gaia Paolini Posted June 28 Share Posted June 28 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? Link to comment Share on other sites More sharing options...
Gaia Paolini Posted June 28 Share Posted June 28 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? Link to comment Share on other sites More sharing options...
Gaia Paolini Posted July 1 Share Posted July 1 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. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now