6.3 Compare Graphs Dynamically by Changing Data Filters
The Copy Columns To.. feature allows you to copy columns from a worksheet to a new worksheet while keeping the two sheets linked. Using this feature you can set multiple Filter conditions on one columns by creating a corresponding number of worksheets. For example to set two Filter conditions on the Column Gas Type we created two sheets Type1 and Type2 that were linked to worksheet Raw.
We also used various features to create a dynamic Graph that would update to reflect the changes in Filter conditions in real time. You can watch the video tutorial explaining these concepts here:
Minimum Origin Version Required: Origin 2015 SR0
What you will learn
This tutorial will show you how to:
- Dynamically compare data with different filter conditions
- Add multiple filter conditions on the same columns using different worksheets
- Add a dynamic filter label in a graph that updates with changes in the filter condition
This tutorial is associated with the Tutorial Data.opj file under <Origin EXE Folder>\Samples path.
Set Up the Worksheets
Open the Tutorial Data.opj file and browse to the folder Compare Graphs with Filters Data and active workbook Book1. The workskeet Raw contains data for the emission of different green house gases in different countries over a period of years.
- Highlight the last three columns : Year, Gas Type, Value
- Right click on one of the columns and in the context menu that opens select Copy Columns To...
- In the Copy Columns to: colcopy dialog that opens, change the Recalculate to Auto so later when input data is changed the result will be automatically updated. Also make sure the DestinationColumn(s) is set to <new>!<new> meaning a new worksheet is added, and click OK
- Name the new worksheet Type1
- Repeat the above procedure and name the second worksheet Type2
- On Sheet Raw highlight column Country and select the Filter button.
- This will create a funnel icon on the top left of the Country column. Click on this and in the menu that opens deselect the Select All check box and check the France check box. Click OK
- The worksheet now only shows information for the country France.
- In Sheet Type1 set a Filter on the Gas Type column for the gas HFCs. Since sheet Raw and sheet Type1 are linked, this worksheet will now show only the HFC emission values for the country France.
- In Sheet Type2 set a Filter for Gas Type PFCs. This worksheet now only shows PFC emission values for Country France.
Creating the Graph and Adding a Linear Fit
- Without anything highlighted in any of the worksheets go to Plot > Multi-Panel/Axis : Stack....
- In the dialog that opens, click on the arrow button next to the Input box and select Select Columns...
- In the Data Browser dialog, make sure that List Datasets = in Current Book and select both the Value columns in Sheets Type1 and Type2. Click Add and OK out.
- In the Stack: plotstack dialog, set Plot Type to Scatter and click OK. This will create the following graph:
- In the newly created graph double click on any axes, in the Scale tab set the Rescale as Auto. Do this for every axes on both plots of this graph so when the worksheet filter is changed and the graph data changes, the axes will rescale automatically.
Note: By holding the Ctrl key and selecting both Horizontal and Vertical icons, you can change Rescale for X and Y axes in the same layer, simultaneously. Use the Layer drop-down at the bottom of the dialog box to apply changes to both graph layers (i.e. Layer 1 and Layer 2).
- Set the Layer drop-down to Layer 1, then click the Tick Labels tab. With Bottom icon selected, change the Display to show just the year.
- Click the Line and Ticks tab. With the Right icon selected, and set Major and Minor Ticks Style to None and Apply.
- Set the Layer drop-down list to Layer 2 and repeat for the Top X axis and Right Y axis in Layer 2 and click OK.
- Delete the two Y axis title objects at the left part of the graph, add a new text label Value and rotate it to 90 degrees (right click and select Properties to change rotation angle) and use it as the new Y axis title.
- Next we're going to do a Linear Fit so the emission trends are more apparent.
- Activate the graph and go to Analysis:Fitting: Linear Fit....
- Set Recalculate to Auto.
- Use the arrow button next to the Input box to select Add all plots in active page. Click OK to close the dialog and perform a linear fit on the two plots.
- Double-click on the linear fit curves and go to the Plot Details dialog, in the Line tab, set the color as Red for each curve. Delete the two legend objects and move away the report table. The resulting graph will look like this:
Add Dynamically Changing Labels
- Click on the Sheet Type1 and select the Filter label row cell in column B.
- Click Edit: Copy: Copy
- Select the Text Tool , and click on the graph upper panel. When the blue text box appears hold down Ctrl + Alt + V. This will paste the Data Filter Label as a link into the text box.
- Click off of the new text label, then right-click and select Properties.
- In the dialog that opens, click on the Programming tab, then check the Real Time check box so the label changes every time the data filter is changed.
- Do this with the Filter Labels in Sheet Type2 and Sheet Raw. Add the words "Gas Type" before the "PFC" and "HFC" labels and "Country" before the Country Data Filter Label. Also Add a Layer Title:
Add Floating Graph to the Worksheet
- To add the graph to the worksheet, activate the worksheet Raw
- Right click on the grey section of the worksheet and in the context menu that opens select Add Graph..
- In the Graph Browser dialog that opens, select the graph we just created, and then click OK
Enlarge the graph by selecting it and dragging the edges. If you change the Filter Types in the worksheet now, the graph will update accordingly.