We’re back!
This tip will be the first one of our fall series. Thank you to everyone who reached out via email to check on the status of the Tip of the Week Blog. It was nice to see how many followers we have who wanted the tips to return.
This week we look at how to use Property Controls and Calculated Columns to create a plot which dynamically updates to show the top ten values of a category, where the category can be selected via a Drop-down Property Control.
Assume we are working with data which shows sales across various departments and the departments are what we want to use as values in the Drop-down. We should create a Drop-down list Property Control which sets values through 'Column selection'.
Notice in the image, in the ‘selectable columns’ field, we chose to manually set which columns to include by writing the following expression:
Name:Electronics OR Name:garden OR Name:Groceries OR Name:Toys OR Name:Furniture OR Name:Clothing
This will ensure that only the departments are shows as options in the Drop-down list. Once we have this created, we now need to create a Calculated Column. This column should rank the department selected from the Drop-down list.
Assuming the Property attached to the Property Control we just created is called whichDepartments, the expression would look like the following:
Rank([${whichDepartments}],"desc") As [Dynamic Rank]
Name the new Calculated Column ‘Dynamic Rank’. Then create a Bar Chart. The Category Axis, for our data set, should be set to 'Customer ID', since we want to show the top ten customers. The Value Axis should be dynamically updated to be the Sum of whatever department is selected in the Drop-down.
To do this, right click on the Value Axis and select ‘Custom Expression’. In the resulting dialog, enter the following expression:
Sum(${whichDepartments})
We then need to make sure the Bar Chart only shows the top ten values. To do this, starting in TIBCO Spotfire 4.0, there is the ability to limit data directly inside an visualization. From the properties dialog, select the ‘Data’ menu and on the bottom, click the ‘Edit’ button next to the ‘Limit data using expression:’ item.
The expression you should add is :
if([Dynamic Rank]<11,True,False)
What this expression will do is only show data where the ‘Dynamic Rank’ column is less than 11 (so the top 10). The ‘Dynamic Rank’ column will update dynamically to re-rank based off what is selected in the Property Control Drop-down. The end result is an analysis file which allows the consumer to select a department and then have the Bar Chart dynamically update to show the top 10 customers. This is much more efficient than creating a Bar Chart for each department.
To learn more about how to use any of the functionality explained in this tip, please consider taking any of our Mentored Online Training courses.
