Candace Hays Posted November 13, 2019 Share Posted November 13, 2019 To Whom It May Concern, We are trying to calculate the average price differential in a text box calculated value field when marking specific months in a bar chart. We have two tables: 1. Revenue and Volumes tables 2. Spot Price Table We are summing the net revenue and volumes by month, calculating a realized price by month, and comparing the monthly realized price by the monthly spot price. And then calculating an average price differential. In spotifre, we created a bar chart with monthly price differentials. We tried calculatingin a text box the average price differential, but we are not getting the same answer as we do in the picture below. Also, we would like to calculate the average price when selecting certain months in the bar chart. Is there away to calculate the average price differential Attached is the excel ss and spotfire project. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 14, 2019 Share Posted November 14, 2019 Thank you for uploading the data and dxp. The problem with your calculated value is that in excel you are taking the average of averages, in Spotfire you are taking the average overall, and the two are different. To check this, you can temporarily remove the x-axis on the bar chart and the height of the single remainingbar will be 0.837. The simple way would be to show the average line directly on the bar chart. (I am not quite sure if you can show more decimal figures though). Otherwise, I managed to display it in the calculated value with a few steps (you go through similar steps in the excel file) 1) create calculated column Price Differential for each [service] as Sum([Gas_Net_Revenue]) OVER ([service]) / Sum([Gas_Net_Volume]) OVER ([service]) / [Gas Spot Price] Noticing that [Gas Spot Price] is already a single value per [service]. 2) create a copy of the Data table, and Pivot it so it has one row per [service] and one column, the [Price Differential] 3) add the calculated value as the average of [Price Differential] over this pivot table. In order for the pivot table to react to filtering the original table, you need to create a relation between Data and Data Pivot via [service], and make sure that when filtering Data, Data Pivot is set to Include Filtered Rows Only' with respect to Data. You can do it by visualising the Filter Panel, locating the filters for Data Pivot and clicking on the double-grid icon on the right. Link to comment Share on other sites More sharing options...
Candace Hays Posted November 15, 2019 Author Share Posted November 15, 2019 Gaia, Thank you for the information. Sometimes we need to filter on a specific set of wells. When I filter on a grouping of wells, the bar chart changes and gives me the correct value but the Average Price differential does not. Is there away to select specific wells In the attached spreadsheet, Pivot tab, the pivot table is filtered to wellnames, location 10, 100, and 101. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted November 15, 2019 Share Posted November 15, 2019 Unfortunately calculated columns do not react to filters, that was not part of the question so I did not know. Your best solution is to use the label of the average line on the barchart. By the way I remembered that you can change the number of significant figures by going into Label and Tooltip of the line in question, select Value(y) and click on the Format button on the right. Link to comment Share on other sites More sharing options...
Candace Hays Posted November 15, 2019 Author Share Posted November 15, 2019 Thank you Gaia. 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