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, at first, that 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 (see disconnected planning). 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.
The Anaplan platform provides a set of functionality for modeling filters in a smart way, using the multi-dimensional power of Anaplan combined to the new native users 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 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:
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.
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, some needs 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 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 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.
3 easy steps are required:
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.
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 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.
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 3 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 criteria 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 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 criteria 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 returns 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 criteria 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)
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, the copy and pasted these codes to the filter grids and the dashboard returned all details of the corresponding four employees.
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 users dimension is not applied to the module, but 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 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:
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 of all, 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 users 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.
In large models and 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
(NOT My Filters.Is Inactive OR Is Inactive) AND
(ISBLANK(My Filters.Name Contains) OR FIND(UPPER(NAME(ITEM(Cost Centers))), UPPER(My Filters.Name Contains)) > 0)
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.
Learn more in the Formula Structure for Performance article in the Community.
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:
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 users 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!
- Guillaume Arnaud