How to plot a comparison line graph from single data table with summary line comparing to details.

[Selicks Cherian]

I have a data table as shown in the below example. The requirement is to plot

1) a line graph with prod_day_zero_offset on y-axis and gas_prod_mcf on x-axis. 

2) One line shows Pad level summary. Average of gas_prod_mcf. The user should be able to select any pad.

3) Other lines show Well level detail of selected wells. The user should be able to select any wells. This will be different wells from different pad. Example: If the user selected P1 as pad for the line in step 2. He will be selecting wells W5 and W6 from pad P2 for these lines.

This is to compare the Pad level average to wells in other pads. 

How to accomplish this? If anyone could shed some ideas to plot this will be great help.

Data table structure

well_name	pad_name	gas_prod_mcf	prod_day_zero_offset
W1	P1	560	0
W1	P1	909	1
W1	P1	1,230	2
W1	P1	1,228	3
W2	P1	908	0
W2	P1	1,028	1
W2	P1	170	2
W2	P1	0	3
W3	P1	102	0
W3	P1	0	1
W3	P1	200	2
W3	P1	363	3
W4	P1	0	0
W4	P1	163	1
W4	P1	475	2
W4	P1	75	3
W5	P2	500	0
W5	P2	900	1
W5	P2	1,200	2
W5	P2	1,750	3
W6	P2	550	0
W6	P2	950	1
W6	P2	1,250	2
W6	P2	1,200	3

 

Edited November 8 by Selicks Cherian

[David Boot-Olazabal]

Hi Selicks,

I'm not certain if I understood your preferred set up correctly, but could it be something like this scatterplot:

In this plot, the gas_prod is plotted on the x axis while the prod_day is shown on the y-axis (your #1).
Then I plotted the average of the gas_prod (vertical line in purple). The filter option will provide for the average per pad, as well as the opportunity to see the accompanying wells. And finally, the wells are listed as color coded lines, which will show the relation between the gas_prod and prod_day per well (also filtered when using the pad filter).

Let me know if this is what you want to see in your visualization.

Kind regards,

David

[Selicks Cherian]

David,

  Thank you for trying to find a solution to my problem. The axis needs to be reversed. on x-axis prod_day_zero and on y-axis gas_prod_mcf. But that doesn't solve the problem I have. When filter to pad P1. It will filter the wells related to the Pad P1, right? That is not what I'm trying to solve. I want to select pad P1 and select wells W4 & W5. I'm trying to compare the average of gas_prod_mcf of Pad P1 to gas_prod_mcf of well W4 & W5, not to the same wells on the pad P1. That is what I'm trying to accomplish. Comparing a pad level summary to a different well that belongs to different pad. 

Thanks,

Selicks Cherian

Edited November 11 by Selicks Cherian

[David Boot-Olazabal]

Hi Selicks,

Would the use of subsets be a solution for you?
When you look at the below screenshot, you see that the filter values are displayed but also the non-filtered ones (in the scatter plot). That way, you can compare the two pads:

You can even add the 'All data' option (or instead of 'Not in current filtering'), so you have the whole data set vs the pads/wells you filtered on:

Kind regards,

David

[Selicks Cherian]

David,

What did you use for line by?

[David Boot-Olazabal]

Hi Selicks,

I used the well name to create the line connection:

Kind regards,

David

[Selicks Cherian]

David,

   The plots kind of helped but not completely. The plot you suggested gives me a trellis by subsets. What I'm trying to achieve is to have a pad level average on the same plot, something like an overlay with wells from different pad.  See the attached excel graph. Black line is average of P1 and the colored lines shows the wells from P2, is there a way to achieve the same? All those shows in a single graph without trellis.

Edited November 11 by Selicks Cherian

[Selicks Cherian]

Also, when you have N number pads and wells, comparing one selected pad average to a not in current filter will be too much to look at.

[David Boot-Olazabal]

Hi Selicks,

You can use a drop down property to select the pad you want to 'summarize'. With a custom expression for the color coding, you then show the selected pad and the other lines are the wells from the non-selected pad:

Whenever you selected another pad, the chart is displaying those figures:

The color coding is based upon this custom expression:

<Case  when [pad_name]=DocumentProperty("Pad2") THEN [pad_name] ELSE [well_name] END>

The DocumentProperty("Pad2")  part refers to the drop down list in the text area (this is a property control):

The aggregation of the gas_prod_mcf column, is an average, as this summarizes the selected pad and shows the average, and displays the individual wells properly per point in time.

Kind regards,

David

 

[Selicks Cherian]

David,

This is exactly what I was looking for. I created the dropdown and set color by with your expression. I get the colors in Color By, but not on the graph See the below screen shot, I think I'm missing something.

When I select few wells that I wanted to compare with the pad, I see only the wells in the Color By and with same color. See the below screen shot.

  What did you use for Line By, Trellis, Lines & Curves, Subsets? Any data limiting? Any Show/Hide Items? I saw something in Trellis in your screen shot, don't know what the setting you were using. Any multiple filter schemes apart from page default filter.

Thanks,

Selicks Cherian

[David Boot-Olazabal]

Selicks,

What version of Spotfire are you working with?
If it's version 14, I can share my dxp file so you can cross check my settings easier.

Kind regards,

David

[Selicks Cherian]

Currently I'm using version 12.5.0.91 in production. But I do have 14 in my test environment. I can open the file in that.

[David Boot-Olazabal]

Excellent.

Please find my example attached.

Kind regards,

David

Scatterplot community.dxp

[Selicks Cherian]

Thank you for sharing the dxp file, sharing the knowledge and help me. That was a really a great help. I really appreciate it.