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.
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!
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
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?