Selectively tally rows from a module based on '(column name) IS x', or similar
I'm trying to write a formula in the module 'Spend (FY18)', that refers 'Budget (FY18)'. I want the formula to check the column 'Period' (which is a pick-list of four options) and if that period equals Q1, it will take the £amount from that row, and add all such rows together to give a £total for that Quarter (repeat for Q2, Q3, Q4). I've tried a few approaches, but something isn't quite falling into place. Apologies if this Q&A exists; I've struggled with the technical term for what I'm asking - Thanks in advance!
Re: Selectively tally rows from a module based on '(column name) IS x', or similar
I've taken a look into your model and come up with a few changes/suggestions that will enable you to get the SUM formula working in your Spend module to give you a running total of budget amount.
The SUM formula in Anaplan is really good for this but the issue you have at the minute is that your spend module does not contain the standard Time dimension and instead contains a custom list called Spend By Quarter. If you try to use the SUM formula in this current setup it won't work as Anaplan will not be able to match the Quarter of each item of spend in the budget module to the Spend By Quarter list.
The other enhancement which I think would be valuable would be to combine your Budget (FY17) and Budget (FY18) modules together and to combine your Spend (FY17) and Spend (FY18) modules together. In the current setup, you will have to create a new set of modules each year going forward whereas if they are combined then you can simply use them going forward with no maintenance required.
Here are the steps you could take to implement this:
Create a new simple list called Years and add 2017 and 2018 to it
Make this new Years list a Parent Hierarchy of your Budget Item #'s list. This allows each item of budget spend you add to be grouped into a particular year for display purposes.
Your new Budget module will then look something like this and can be used to hold all items of budgeted spend regardless of which year we are talking about.
New Budget Module
Rebuild your Spend module to use the Time dimension (set to Quarters) as oppose to using the custom Spend By Quarter list
With this in place, you can now add the correct SUM formula to give you the running total summed by Quarter as shown below: