In a previous tip, we discussed how to leverage Property Controls to create a Tip 10 list based off various categories. This tip assumed the data was setup in such a way that each category was its own column.
(For example, every row had a customer id, as well as a how much that customer shopped in each of the 6 departments (clothing, furniture, toys, groceries, electronics, and garden) and we wanted to find the top 10 customers for any given department selected by the user in a drop down)
What if the data is setup differently so that each category is in a single column? We can accomplish the same output, but will need to follow a few different steps.
Assume our data is structured like the Data Table below. . .
. . . and we want to create a Bar Chart that allows us to display the top 3 groups from the ‘Group’ Column based off a specific value for ‘Cat A’. For example, which 3 groups with the value for ‘Cat A’ of True had the most ‘Value’.
To accomplish this, we first need to create a Drop-down list Property Control to display unique values from the ‘Cat A’ column.
Using what we learned from another previous tip, we then create a calculated column which will either output ‘Yes’ or ‘No’, depending on whether the value from ‘Cat A’ matches the value selected by the user in the drop down we just created.
if(find([Cat A],"${CatAFilter}")>0,"Yes", "No" )
We should then uncheck the ‘No’ value in the Filter Panel for this newly created Calculated Column. Now, the resulting data only shows values that match the drop down. If the user selects ‘False’ in the drop down, then only rows in ‘Cat A ‘ where the value is ‘False’ should be displayed as shown below.
As we mentioned in the previous tip , it is a good idea to hide the Filter for the newly created calculated column, so that users in the Web Player do not accidentally update or reset it.
Once we have this, we now need to rank the remaining rows. To accomplish this, we will create another calculated column. This will use the Rank function, to rank in descending order the ‘Value’ Column grouped by the ‘dynamicFilter’ calculated column we just created.
Rank([Value],"desc",[dynamicFilter])
Next, just like in the previous tip, we create a Bar Chart to show the ‘Group’ values on the category axis, and on the value axis, we use the ‘Value’ column. We also configure the Bar Chart to sort the Bars by height. We then use the ‘Limit Data Using Expression’ property to display only the top 3 Groups.
if ([Top 3]<4, True, False)
To extend this, if you wanted to display the Top 3 based off values from both ‘Cat A’ and ‘Cat B’ columns, you would create another Dropdown list Property Control to display unique values in the ‘Cat B’ column and you would update the ‘dynamicFilter’ Calculated Column to the following expression:
if(find([Cat A],"${CatAFilter}")>0 AND find([Cat B],"False")>0,"Yes", "No" )
Interested in testing your skills to see how much you know about authoring in Spotfire? Try our newly released Spotfire Author Assessment. It is a 60-question exam covering all topics related to authoring and report development in TIBCO Spotfire Professional. The exam is hands-on and requires students to not only understand available features and functions, but also how to navigate the Spotfire Professional User Interface , and how to take data and business questions and come up with solutions using TIBCO Spotfire. The exam requires students to have TIBCO Spotfire Professional 4.x or higher installed.
