In this post i’m going to show quickly two methods which I used to create a ‘show all’ option alongside a ‘top n’ option.
Before we start, the visualisations produced in this blog can be found here, so feel free to download them and break them down yourself.
Option 1. Two parameters, one set and one calculated field.
- Create your first parameter. This will be our ‘Top N Parameter’ it will be of either float or integer type and should have all the ‘n’ values which you wish your user to select from.
- Create your set. This will be based on your dimension that you wish to view the ‘Top N’ of.
Navigate to the ‘Top’ tab on the create set window, then select ‘By field:’ from the drop down of the numeric value, select the parameter you have just created; and of course select your method for determining your ‘Top N’.
- Create your 2nd parameter. This parameter will be used to determine whether the or not we wish to show the ‘Top N’ or we wish to show all. It will be of string type and contain these two text strings.
- Create a calculated field which determines whether we should use all the values, or only those that meet the criteria given in the set based on the selection from our 2nd parameter.
- Drag this calculated field onto the filter shelf and select to only include those where the result of the calculated field = True
Option 2. One parameter and three calculated fields.
I developed this second idea knowing that the first isn’t the best user experience, ideally you would want your user to be able to select the ‘All’ value from within the Top N drop down.
But this causes a problem, our parameter type must be a string.
Then this causes a problem because our set cannot work off a string field.
So here’s how…
1. Create your parameter. This will be our ‘Top N or Show All’ parameter it will be of string type, and you will have to list the N values which you wish to include, alongside the ‘Show all’ value.
2. Create a calculated field which simply equals the parameter value
3. Convert the field type of this column to be of a numeric type (this will just create a NULL value if ‘Show All’ is selected within the parameter).
4. Create a second calculated field. This will be used to determine whether the value is within the ‘Top N’.
5. Create your third calculated field. This will be placed on the filter shelf and will be used to determine whether to return all values or simply those that meet the conditions of the second calculated field.
6. Drag this calculated field onto the filter shelf and select to only include those where the result of the calculated field = True