If you would like to use 2 unrelated filtering conditions on one column, you will need to create a custom filter in the dashboard. An example of this would be reporting the quantity sold for the top 3 selling categories along with the quantity sold for the produce category. In order to do this, OR based logic would need to be used to combine the two filtering conditions. To do this, a user will need to create the filters in the widget editor individually, and copy the code generated by these filters into a template.
Creating custom filters gives a user new ways to create dashboards that will meet their requirements.
The following image shows the basic pivot table that we will create a new filter for. It is sorted by Total Quantity.
I would like to filter by two separate metrics: Top 3 Categories by Total Quantity, and where Category=Produce. If we used AND logic to combine these filters, no data would be returned, because there is no data that is in both the Produce category and in one of the top 3 categories.
To get the required data, we will need to use the 'Advanced' tab in the filter editor to create a custom filter.
Filter #1- Top 3 Categories by Total Quantity
First, create the filter to get the data for just the top 3 categories by Total Quantity. The image below shows how to create this standard filter.
Once this has been created, go to the Advanced tab in the filter menu in order to view the filter code.
Copy the code from this into a text editor, such as Notepad.
Filter #2- Category Name= Produce
Delete the filter that was created in Step 1, and create a new filter that selects just produce. This can be done with the Text or List tabs in the filter editor. Go to the Advanced tab and put the code generated filter #2 below the code that was generated for filter #1.
Combining the Filters
Currently, you should have two sets of filter codes in a text editor. Now, we need to write a small amount of code to combine these. In the image below, the green text is code you will add, and the grey text is the code that was generated in steps 1 and 2. I’ve broken down what you need to add into steps, and added details below the image. (This template is attached as template.txt)
1. Starts the filter code
2. Combines the filters in the brackets with OR logic
3. The comma separates the filters
4. Lets SiSense know this is a custom filter
5. Ends the filter code
Putting the Code into SiSense
Create a new filter for the widget, and go to the Advanced tab. Remove any code currently in there, and insert the code you created by combining filters. Press Test to make sure the code is correct, and then press OK.
This new filter fulfills the requirements of the Category is in the Top 3 by Total Quantity or the Category=’Produce’. The image below shows the final result set. The individual and combined filters have been attached.