Anaplan users are often moving from an Excel environment (with the flexibility to filter, search, and sort data according to their specific criteria) into a new Anaplan environment that is much more standard, structured and based on pre-defined dashboards.
Although our Anaplan dashboards and platform provide a lot of benefits in terms of speed, power of calculation, standardization, and collaboration, it is likely that, at first, users can’t customize their views and filters as they used to and therefore ask for the ability to export the dashboard’s data into Excel. In Excel, they would use the necessary filters and searches to review their numbers and/or edit the plans before re-importing the modifications in Anaplan.
Exporting data out of dashboards to work in Excel not only takes time, but it also creates potential misalignments. Data changes often in Anaplan models, which makes all previous exports obsolete, leading users to make decisions or discuss business issues on potentially outdated numbers. In some cases, it might even create potential performance issues due to the blockage of models during these import/exports.
The problem we are trying to solve for: How do we keep our users in the Anaplan platform to do their work, which requires a higher level of advanced customization, faster and more easily than their previous Excel environment?
The solution is called “smart filters.” These smart filters have proven to make a significant impact on the adoption of the Anaplan platform in some of our key customers.
How do we solve this with smart filters?
The Anaplan platform provides a set of functionality for modeling filters in a smart way, using the multi-dimensional power of Anaplan combined with the new native user's dimension, to calculate very advanced and user-specific, data-driven exceptions.
Planning based on data-driven exceptions are best practices and are used to help users find outstanding data such as out of thresholds, missing, flagged, changed, unexpected variances, etc.
What is different from standard filters is the ability to make the value of each filter criteria specific to each user (as it was in user’s private Excel workbook), but with the full power of the Anaplan platform. In addition, these user-specific criteria are made persistent in the Anaplan model; the next time a user opens a dashboard, he/she can find the latest used criteria and does not have to enter them again and again.
In short, smart filters are enabling multi-dimensional, exception-based planning, customized by the user.
This customer is doing manual assignments of accounts to territories; 2 million accounts across 2,000 territories are to be assigned and verified.
Users need to constantly check and adjust these assignments and have business questions such as:
Show me all accounts belonging to a specific region
Let’s see and adjust how all subsidiaries of “Account ABC” have been assigned through the territories
Show me all accounts in my regions where the re-allocation to new territory is awaiting approval
Show me all the accounts where the planning group is still missing
Show me all accounts, just modified by the assignment process
The filtering is key in the above cases, as users need to use many criteria, and some of them are advanced hierarchical-based calculations, such as “include all descendants and ancestors of this given set of accounts matching these criteria.”
And, of course, the criteria used for verification differ from one user to the next.
Here’s a screenshot where up to 12 criteria can be used: Customer 2
This use case is Global Accounts Quota plans; users need to edit and review accounts quotas, based on criteria such as country, region, but also accounts where the quota has been ‘overridden’ by other users and accounts where the quota is greater than a threshold.
Customer 2 also had another use case on Comp Planning; users need to filter reps based on their attribute or their assigned comp plan attributes, or based on what sales org they have been assigned to. Attributes are based on booleans, or formatted list items as drop-downs, or free text:
Supply chain plan: users need to dynamically display “seasons” they want to plan on or review.
Some users need to see the summer season, some need to see the full four seasons, some need to see the spring season, and some need to see the full plan horizon.
The smart filter allows the user to pick the season selection once, and all grids of a dashboard will filter based on this custom selection.
This use case is demand planning by product and customers; customer 4’s demand planning users need to filter their product lists based on 13 criteria as shown below.
Because entering up to 13 criteria can take some time, and more importantly, because users need to often switch from one set of criteria to another one, we implemented a solution to allow users to save multiple instances of the 13 criteria and give a name to each instance. Then they can easily pick a filter by its name, which will use all related criteria.
This implementation had a positive impact on usability by significantly reducing the number of clicks compared to what users had to do in Excel and by eliminating potential mistakes in filtering.
See below, where the user chooses “filter 1,” applies the filter, and all 13 of the criteria are filled in.
Then, the user chooses “filter 2,” applies the filter, and sees different criteria.
This can be easily extended with having user-specific filters, where everyone can create their own set of criteria and name it, or a common set of filters.
How to model smart filters
3 easy steps are required:
Create the filter module
Add the user filter line item(s) and set their formula
Apply the user filter to the dashboard
First, create one module that you’ll name “My Filters,” that is dimensionalized by users only.
Then create one line item per filter criteria you want to use across the model.
Create one module only, even if you’ll use smart filters in many dashboards.
Example (Customer 1): User filter module
Next, create a new module that you’ll need to filter the dimension(s), based on the criteria you just created in the “My Filters” module.
Dimensionalize this module by these dimensions, and by the native “Users” dimension, and create one line item of type boolean that you’ll name “user filter.”
Here is a basic example:In some cases (Customer 2 Global Accounts), the filter criteria are based on the data of a module, so it should apply to more than one or two dimensions; a smart filter such as “Quota $ is greater than,” where "Quota" is by account, product and time implies that the user filter is by account, product and time and User.
This type of data-based smart filter should be used only if the model size allows it.
User filter formula
There is a very simple way to generate the user filter formula and keep it simple and readable even when there are 20 criteria used.
Let’s use a generic example, where we want users to filter a list of cost centers based on country, industry, and size, and view a few KPIs.
My filter module is:
The module to be filtered where we use the user filter is:
The user filter formula (in the New user filter module) based on the three criteria is:
(ISBLANK(My Filters.Country) OR Cost Center Details.Country = My Filters.Country) AND (ISBLANK(My Filters.Industry) OR Cost Center Details.Industry = My Filters.Industry) AND (ISBLANK(My Filters.Category) OR Cost Center Details.Category = My Filters.Category)
In short, each criterion is within an AND clause.
The clause is True if there is no filter criteria entered (it’s best to show all if no filter is entered than show nothing) OR if the criteria entered equals the attribute: Cost center.Country = UK
With such formula structure, you can add as many criteria as you need by copy-pasting and adjust to the next criteria, and you’ll keep it maintainable.
If you add a fourth, the criteria to the smart filter of type boolean, such as “Is Inactive,” then you’ll add a fourth clause to the formula, separated with an AND statement. This fourth criterion is true if the new criteria is unchecked (false), or if the criteria is checked and the cost center is set as inactive.
(ISBLANK(My Filters.Country) OR Cost Center Details.Country = My Filters.Country) AND (ISBLANK(My Filters.Industry) OR Cost Center Details.Industry = My Filters.Industry) AND (ISBLANK(My Filters.Category) OR Cost Center Details.Category = My Filters.Category) AND (NOT My Filters.Is Inactive OR Cost Center Details.Is Inactive)
From a usability perspective, it’s important that, by default, filters return all items so that the grids never show empty the very first time. All filters are empty or unchecked.
This is why having a user filter of type boolean should return all items when the criteria are false. This is why we’re creating the above filter as “Is Inactive” and not ”Is Active.”
You can add a fifth criterion of type Text. This is used for a wild card search/filter, such as “Show me all accounts, which name starts contains “Bank of Am.”
In that case, add a new line item of type text in the “my filter” module, and add an AND clause to the user filter using the FIND function combined with the UPPER() function, which allows finding the text string regardless of lower case/upper case.
Here’s the example:
AND (ISBLANK(My Filters.Name Contains) OR FIND(UPPER(My Filters.Name Contains), UPPER(Cost Center Details.Cost Center Name)) > 0)
Another frequent requirement is the ability to enter multiple text strings in the criteria. In our example, it could be “Show Me Cost centers A, B, and C.” In that case, just change the order of the find function to:
AND (ISBLANK(My Filters.Name Contains) OR FIND(UPPER(Cost Center Details.Cost Center Name), UPPER(My Filters.Name Contains)) > 0)
Here you’ll find all the cost centers whose name is included in the text string “A,B,C.”
This only works if the text string contains the full name (or code) of the list item (in this case cost center). Customer 2 was using employee codes; users know the exact four employee codes they want to edit. As a result, they copy and pasted these codes to the filter grids and the dashboard returned all details of the corresponding four employees.
Adjust the dashboards
Once your boolean is working as expected, use it as a filter in the grids of your dashboards as needed. Here’s an example:Notice that users have “Current User” automatically selected. This allows the filter to be user-specific, even though the user's dimension is not applied to the module, but it's just in the user filter module boolean.
This functionality is only possible if you use this native user dimension.
Next, publish the “my filters” module to the dashboard, to provide users with all criteria, just above the grids they want to filter. You can apply a “Clear” style to it to distinguish it from other grids. Keep in mind that the dashboard requires it to be refreshed when the filter is changed. You’ll need to educate users to find the refresh button on the menu bar:
Although the alternative is to create a refresh button, that is actually reopening the same dashboard, which we don’t recommend using for the following reasons:
Because it takes extra time to create, publish, and reposition the button
Because button names need to be unique across the model, so you’ll end up with Refresh A, Refresh B, Refresh C, etc.
Because it’s a good opportunity to expose users to the menu bar where they can find additional features, especially the “undo” feature, that many users aren't familiar with in the platform Learn more in the Undo an Opertation Anapedia article.
Size and performance considerations
Applying smart filters has a cost in terms of model size, but it’s very reasonable compared to the benefits these bring to users and to adoption in general.
First, the user filter line item is a boolean, which costs 1/8th of the memory footprint compared to a text line item, and 1/4th of a list formatted line item. So even if we add several million cells, the memory impact is not that large.
For example, on one of the largest models, Customer 1’s user filter boolean took 2MM accounts * 100 users = 200MM boolean cells. It worked perfectly fine in terms of performance.
Customer 2’s global account user filter is applied to quota data, so dimensionality is: region, account, product family, users => The size was only 1.5MM cells, as this model was on summarized levels of accounts and product. Performance of applying the filter was excellent.
We obviously would not apply such filters if we had 2MM accounts * 2000 products * 8 regions. Remember, the line item cannot exceed 2.1B cells without time or versions
Be aware that the native user's dimension does not support subsets, so, it’s important to remove non-active users from the model as these will take up cell space. Learn more in the Work with the Users Lists Anapedia article.
Fine tune model performance
In large models where the smart filter can be complex with 20+ criteria, such as Customer 1, we had to split the user filter into multiple line items, in order to keep the performance of changing filters to less than 3-4 seconds.
Following up on the filter example above, where user filter initially is in one formula:
(ISBLANK(My Filters.Country) OR Cost Centers.Country = My Filters.Country) AND
(ISBLANK(My Filters.Industry) OR Cost Centers.Industry = My Filters.Industry) AND
(ISBLANK(My Filters.Category) OR Cost Centers.Category = My Filters.Category) AND
What works faster, but takes more cell space is to create one user filter per criteria:
User Filter 1 = (ISBLANK(My Filters.Country) OR Cost Centers.Country = My Filters.Country)
User Filter 2 = (ISBLANK(My Filters.Industry) OR Cost Centers.Industry = My Filters.Industry)
User Filter 3 = ISBLANK(My Filters.Category) OR Cost Centers.Category = My Filters.Category)
User Filter 4 = NOT My Filters.Is Inactive OR Is Inactive)
User Filter 5 = ISBLANK(My Filters.Name Contains) OR FIND(UPPER(NAME(ITEM(Cost Centers))), UPPER(My Filters.Name Contains)) > 0)
Then, create a sixth line item for your dashboard that you’ll call ”final user filter,” which is calculated as ‘User Filter 1’ AND ‘User Filter 2’ AND ‘User Filter 3’ AND ‘User Filter 4’ AND ‘User Filter 5.’
This is performing better as the final filter only recalculates what is required based on what user is changing, instead of recalculating the whole thing.
In the near future, we should see an improvement to the dashboard filters which also allows you to address custom filtering by end-users. The quick filters will work similarly to Excel filters, where the user can right-click on each column of a grid and select values to filter on. Once available, these quick filters should be used for simple filtering on the grid.
Smart filters, as described here, are recommended for:
Centralized master filters–defined once and applied in many grids in one or more dashboards
Data-driven exception filtering
Filtering online items not necessarily displayed in the grids, or not necessarily present in the module that is being filtered
Filters that need to stay persistent per users, on one or more instances (like for Customer 5 above)
In conclusion, smart filters are a proven solution to keep our users in the Anaplan platform to do their work and provide them a high level of advanced customization, therefore reinforcing adoption and customer satisfaction.
You can design smart filters through the implementation of standard Anaplan modeling functionality using formulas, dashboard filters, and implementation of the native user's list.
Be cautious of model size impact, but don’t be intimidated by it. Smart filters have been deployed to production on very large customer models successfully.
Please help us make these smart filters even better by sharing your experience. Thanks for reading!
The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
I'm an Anaplaner since 2012. As a Master Anaplaner, I focus on complex model building implementations, help our customers & partners on model best practices and COE, contribute to building scalable architecture and usable applications. My key focus is to make our Connected Planning story a reality at our biggest customers.