Simple yearly average

Hi All

having completed the 102 course I am in the process of building my first model, so please be gentle if I have asked a ridiculious question but I really cant figure this out.

I have a line item showing balances across time periods and I would like a second line that shows the average for the financial year for the same time periods, I feel like this should be really easy but having read through parts of Anapedia and the forum I feel like I'm getting nowhere.

I believe it is something to do with timesum but I have yet to figure out how to identify the start and end periods for a financial year to feed into that calculation.

Please help!

Stewart

Accepted answers

DavidSmith

Yep

This assumes uniformity across all other dimensions, meaning you don't need to calculate the actuals for other list members

1. Create a time settings module (if you haven't already got one)

2. Add a line item (e.g "values to count", ) and enter "1" against each applicable month. Hopefully you can calculate this from the data somewhere so it is not manual, but let's start simply first!

3. The year summary will then add up the number of months with actuals

4. In your formula, refer to the line item from 2 (e.g. YEARVALUE(values to count) as the denominator

If you need to be specific in the target module, unfortunately we don't have an average function that works like excel to only count values so:

1. Create a line item as 2. above in the target module using something like IF values <>0 then 1 else 0

2. Use this line item as the YEARVALUE(xx) denominator

Hope that helps

David

All comments

DavidSmith

@stewart.jardine

No issues with asking, that is what this forum is for!!

There is a function called YEARVALUE() which returns the total for the year for a time based line item

So to get an average you can do YEARVALUE(Line item)/12, for months, YEARVALUE(Line item)/52 for weeks etc.

That assumes you're using standard timescales

You can also use an Average summary method in which case the divisor is not needed

There are further "rolling" calculations you can do using TIMESUM and MOVINGSUM, but unless you need the average to move each month, you should be good with the above

I would ask why you need the same value in each month. That could be calculating more than you need. If you need to perform a calculation, you could create a module by year to hold the average and then refer to it in the calculation

I hope that helps and welcome to Anaplan modelling!

David

I hope that helps

stewart.jardine

thanks David, appreciate the quick response, I had looked at using the yearvalue function but from my understanding it was restricted to calculating using the same summary method as the line item I am getting the value for.

I would like to show the closing balance as the summary function for my balances and as such have set it up that way and if possible would rather not adjust that just to get this sum to work.

the reason I am trying to get the same value in each month of the financial year is actually that this is the first part of a more calculated formula that will divide a yeartodate() figure by the result of this formula e.g.

x = yeartodate(writeoffs)/[financialyearavearge](balance)

with [financialyearavearge] being the headache I'm trying to resolve

Stewart

DavidSmith

Ok, makes sense, and you are correct, YEARVALUE won't work here due to the summary method

The simplest way here is to have a second line item with the summary method as SUM and use that in the calculation

There are other space saving methods, but due to the issue of summing out of a timescale (due to block structure of Anaplan), these get quite involved, so let's just keep it simple!!

David

stewart.jardine

thanks David, I have now created another line item that is a sum and allows me to use the yearvalue function however this has pointed out another small issue... for my first year of actuals I don't have 12 months worth of data and so taking my newly created yearvalue and simply dividing it by 12 doesn't work. is there a function that will generate the number of months present in any given financial year?

DavidSmith

Yep

This assumes uniformity across all other dimensions, meaning you don't need to calculate the actuals for other list members

1. Create a time settings module (if you haven't already got one)

2. Add a line item (e.g "values to count", ) and enter "1" against each applicable month. Hopefully you can calculate this from the data somewhere so it is not manual, but let's start simply first!

3. The year summary will then add up the number of months with actuals

4. In your formula, refer to the line item from 2 (e.g. YEARVALUE(values to count) as the denominator

If you need to be specific in the target module, unfortunately we don't have an average function that works like excel to only count values so:

1. Create a line item as 2. above in the target module using something like IF values <>0 then 1 else 0

2. Use this line item as the YEARVALUE(xx) denominator

Hope that helps

David

stewart.jardine

thats done it, thanks

Quick Links

Can you help?

Unanswered questions

Has anyone taken a stab at Master Anaplanner Recertification exam yet?
Just curious :)…
Solution for indent diabled for hierarchical list when the page has more than two axis
Hello fellow Anaplaners, I have a question about the UX for Anaplan for hierarchical list. If I have a list with multiple layers, say P1, P2, P3, and P4, and the workpage had 2 axis, list and line items, in its vertical axis and time in its horizontal axis (see figure below) The list is shown without indentations, and may…
Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…