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!

Accepted answers

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

Screen Shot 2017-08-18 at 16.57.08.png

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:

Screen Shot 2017-08-18 at 16.58.33.png

Hope this helps!

All comments

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.

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.

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

Screen Shot 2017-08-18 at 16.57.08.png

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:

Screen Shot 2017-08-18 at 16.58.33.png

Hope this helps!

matthew_antos-l

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

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…