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!
Solved! Go to Solution.
Thanks for trying out our new forums!
You want to check out our "[ SUM ]" function.
Your formula will be something like this:
'Budget (FY18)'.'Amount (£)'[SUM: 'Budget (FY18)'.Period]
You should also consider using our built in Time list rather than a general list.
You would have to change the settings in the Spend module, and then change the format for the "Period" line item to the Time format "Quarter".
The formula would be the same as above.
Hope that helps.
Hi Mathews your first modules is like a database now :) lets call it module 1.
Make sure you would have all the required lists which are used in the line items for the list formatted purpose in your module and build another module called Module 2.
Then once you have module 2 with all the required lists then you can just use the SUM Formula to get the desired results.
SUM function where a line is associated with a time period (i.e. month, quarter or year) will return the results described.
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:
New Budget Module
Hope this helps!
Thanks Karl, this is brilliant, completely revolutionised my model (and my modelling). Cheers!