It’s been a long-requested feature, and today we’re happy to announce that Analyse-it version 5.10 now includes the ability to save the dataset filter with an analysis and re-apply it on recalculation.
Analyse-it always allowed you to use Excel auto-filters to quickly limit analysis to just a subset of the data, but until now that filter wasn’t saved. Each time you recalculated the analysis it was based on the currently active filter rather than the filter in-effect when you created the analysis.
This “active filter” method had its uses in exploratory data analysis: you can easily create an analysis, adjust the filter criteria, click Recalculate to see the changes to the analysis, then repeat as necessary to explore the data. But it also had its limitations. For example, if you created two analyses to look at subjects where Age > 20 and Age <= 20, simply clicking Recalculate on those analyses could get you in a mess if you didn’t reset the filter conditions on each analysis before recalculating.
Thankfully Analyse-it will now take care of that for you.
In Analyse-it version 5.10 and later, when you create an analysis (or edit an existing analysis), you will see a new command on the Analyse-it ribbon called Filter. If you click the drop-down arrow alongside the command you will see two options:
For new analyses, Save Filter & Re-apply is selected by default so any filter criteria you apply during analysis will be saved and re-applied when you next recalculate or edit the analysis.
For existing analyses created before version 5.10, Use active filter will be the default and those analyses will continue to use the filter in effect when recalculating. That maintains the behavior users of earlier versions expect, so they’re not surprised by the new feature, but you can click Edit and change the Filter setting if you want to save the filter with the analysis instead.
The filter criteria used calculating an analysis shows in the header of an analysis report, see below:
Space is limited in the worksheet tab name, but a short, truncated description of the filter criteria is included. Usually, you are better renaming the worksheet tab to better describe the filter in a clearer, succinct way. For example, Analyse-it might label the tab “Height (Age > 20 and Sex = ‘Mal..”, truncated due to limitations on the length of a worksheet tab name, but you might rename it to “Male Height over 20y/o”. When you change a worksheet tab name Analyse-it will respect the change and will leave it as-is during recalculate.
When a filter is saved with an analysis you will notice a new command on the Analyse-it analysis ribbon called Filter, see below:
When you click Filter it applies the filter criteria saved with the analysis to the dataset and shows the dataset. You now have a few options. You can see the dataset filtered by the criteria. Or you could change the filter criteria, switch back to the analysis, and click Recalculate to change the filter applied to the analysis. Or you could create a new analysis, based on the same filter criteria as the existing analysis.
The Filter command is disabled if the analysis is using the active filter, rather than a saved filter.
Save filter works with almost all filter conditions: filter by value, by condition, by color/fill/icon, above/below average, top/bottom items, and so on. But there is one limitation: filtering to multiple dates, such as shown below:
If you filter to more than 2 dates, add-ins such as Analyse-it cannot retrieve the list of values. This is a limitation of Microsoft Excel that unfortunately hasn’t been remedied in the last few years so it must be low on Microsoft’s priorities. If this is seriously affecting you, please get in touch with Microsoft and ask them to resolve this limitation for add-in developers.
If you have active maintenance you can download and install the update now, see updating the software. If maintenance on your license has expired you can renew it to get this update and forthcoming updates, see renew maintenance.
Let us know your comments, opinions and any questions you have on this post. Your feedback is always appreciated.