The Bedford Coach - Advanced Line Item Subsets – Filtering
Advanced Line Item Subsets – Filtering
A line item subset is a list of items drawn from one or more modules:
The items in a line item subset can be drawn from a single module or from a variety of modules.
There cannot be more than one line item subset used in a single module
An individual line item can be used in more than one line item subset.
It can seem like a tricky aspect to Anaplan when first learning, but the understanding of Line Item Subsets can unlock a very powerful function, which can be used across a number of use cases.
An FP&A user wishes to either submit a manual entry against a Cost Account or have that Cost Account driven by a % of another Account, e.g. % of Revenue. The administrator will select which method of entry is appropriate per Cost Account ahead of the planning cycle and only wants those particular methods to appear on the users dashboard. The following is an example of how we might approach this in Anaplan using Line Item Subset Filters.
I have created a simple P&L Planning module with the following lists. Time, Version and Account. For each account type planning numbers are either typed in by a planning user or uplifted from the previous year. The challenge comes when this module is published to a dashboard, I only want to show the relevant planning type (% Uplift or Input) against the relevant account but show the Total on a consolidated level. Seems simple enough with a small account structure, but when you are working with 100’s of accounts, that is where it gets a bit tricky.
Create a Line Item Subset [LISS] using the ‘P&L Planning’ as the source module and ensure all line items are ticked for inclusion. When naming a Line Item Subset I like to prefix with a ‘LISS – ‘ in order to easily distinguish it from a standard list.
Don’t forget you can add/remove line items in any subset within the Blueprint view of the source module.
Create a new module called ‘Account Assumptions’ with Line items for setting input level for account and various types of planning methodologies.
Once the module is created enter the value 1 as formula for the ‘Account Level’ Line item setting the summary method to NONE. This will ensure that only the lowest level accounts have a numeric flag against them and none of the consolidated levels. This flag will be used later to identify which planning methodology to display against which account (level of account)
You can now set the planning method against each of the accounts. For the purpose of this exercise we are applying a manual entry, but these can always be driven by a formula or equivalent.
After setting each of the input account’s planning methods, create another line item in the Account Assumptions module called ‘Filter’. This line item will have the same lists as per the module with the addition of the Line Item Subset. You will notice that this line item will have an icon indicating a subsidiary view. This means that the dimensionality (lists that apply to the line item) is different than that of the overall module.
Write a formula in the Line item that specifies if the item from the line item subset is not Total (for the purpose of the example we specified the exact items, but you can say any item but the ‘Total’) and the Account Level is 1 then pick it from the Boolean item in the module, else if the item from the line item subset is the ‘Total’ and the account Level is not 1 (which will be all consolidated levels as we have set the summary method to NONE). As we are writing a condition to apply to a consolidated level in the account structure, we’ll also need to set the summary method to Formula.
Now that the filter highlights which item should be shown against which account, we’ll now be able to use this filter line item when filtering the view in the ‘P&L Planning’ module. This is possible because Anaplan would recognise that the Line Items Subset used in the filter line item comes from the source it is being applied to. NOTE: once the filter is applied, you can hide the line item column in the view to make the input/interaction seamless for the user.
Tip: To hide the line item column, simply set the column width to 6.