Selectively tally rows from a module based on '(column name) IS x', or similar

matthew_antos-l

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!

 

karl.mcphee

Hi Matt,

 

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:

 

Hope this helps!

simon_ritchie

Hi Matt,

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.

Simon

harish_bk

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.

Mark_W_Shemaria

SUM function where a line is associated with a time period (i.e. month, quarter or year) will return the results described.

matthew_antos-l

Thanks Karl, this is brilliant, completely revolutionised my model (and my modelling). Cheers!