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
Best Answer
-
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
0
Answers
-
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
0 -
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
0 -
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
0 -
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?
0 -
thats done it, thanks
0
