
1. Re: How to average latest 3 months percentage?
Mark Fraser Jun 8, 2016 2:19 AM (in response to Yew Seng Lai)Hi Yew
I have to admit I didn't read your question really thoroughly, is the expectation to calculate the % in a different way  you have 2 percentages  85.88% and 88.55%?!
Anyway, I have something working for you (the 85.88%) but its not currently dynamic, yet.
Here is what I have so far 
Formulas
Sales/Target >> SUM([Sales])/SUM([Target])
Period Sales >> LOOKUP(RUNNING_SUM(SUM([Sales])),LAST())LOOKUP(RUNNING_SUM(SUM([Sales])),FIRST())
Period Target >> LOOKUP(RUNNING_SUM(SUM([Target])),LAST())LOOKUP(RUNNING_SUM(SUM([Target])),FIRST())
Perc% then is simply >> [Period Sales]/[Period Target] (they are already aggregated)
As mentioned, it is not a dynamic solution. Id like to use parameters to control the number of months to look back.
This solution uses the FIRST() and LAST() rows, and so as the data increases, the formulas will update but I'm not sure if you're looking for a rolling calculation or fixed period.
Hope something above helps!
9.3.1 workbook attached.
Cheers
Mark

208522.twbx 13.2 KB


2. Re: How to average latest 3 months percentage?
Yew Seng Lai Jun 8, 2016 11:06 PM (in response to Mark Fraser)Hi Mark,
Thanks for the formula, but the % that i want is 85.55%, not the 85.88%. If you put avg up the latest 3 mths % in excel, it will give you 85.55%...possible to get 85.55%?
Yes, it is a fixed period, always looking for 3 mths latest. My data is always 15 mths data, so I think I just minus 12 instead of 1..

3. Re: How to average latest 3 months percentage?
Mark Fraser Jun 9, 2016 6:19 AM (in response to Yew Seng Lai)Hi Yew
I see what you mean...
You need both the average (7300/8500 = 85.88%) as well as the average of Sales/Target ((2000/2500)+(2500/3000)+(2800/3000))/3 = 85.55%)
Just how to do it?!
I'm sure it can be done, but everything I have done so far hasn't been pretty.
FYI  I am trying both FIRST() and LAST() as well as WINDOW_AVG and/or RUNNING_AVG, maybe INDEX() and/or LOOKUP.
The answer is somewhere in there I'm sure!! Fun problem
I'll also ping Simon Runc
I have the D6 figure, its the E5 figure ((2000/2500)+(2500/3000)+(2800/3000))/3 = 85.55%) we are trying to achieve... hopefully dynamically.
Cheers
Mark

4. Re: How to average latest 3 months percentage?
Simon Runc Jun 9, 2016 6:45 AM (in response to Mark Fraser)hi Mark,
Thanks for the ping...I've only had a quick read through, and have come up with this formula
WINDOW_AVG([Sales/Target],2,0)
and then used compute using = month...As you can see we get the 85.56% against the last month. This is dynamic in that it runs the last 3 for every month (rolling), but we could add a condition (or Last(), First()...etc.) if we only want this for the last month?
Looking at all the RUNNING_SUM work to get these targets...feel a little like Ruud van Nistelrooy (with your base country and euro2016 round the corner...hope you appreciate the reference) in that...all the hard work has been done, I come in put it in the back of the net (from 2 yards!!)

208522  SR.twbx 14.9 KB


5. Re: How to average latest 3 months percentage?
Mark Fraser Jun 9, 2016 7:11 AM (in response to Simon Runc)1 of 1 people found this helpfulThank you Simon, I was hovering around that, but kept over complicating it, you star!
Its been too long since I got involved in some nice table calcs, I have to admit, I enjoyed it!
Happy for me to lay it up, and you to score
Alas, no Oranje army at the Euros!!
None of this >> https://www.google.nl/search?q=bavaria+jurkje&espv=2&biw=1920&bih=995&tbm=isch&tbo=u&source=univ&sa=X&sqi=2&ved=0ahUKEwj…
That said, I will always support England, I enjoy the pain of disappointment and penalties too much!! haha
@Yew
As Simon recommends, swapp the Perc% calc to WINDOW_AVG([Sales/Target],2,0) (set to month)
And as Simon also recommends, if you only want the last month, swap the formula to
IF LAST() == 0 THEN WINDOW_AVG([Sales/Target],2,0) END
Hope that helps!
Cheers
Mark

6. Re: How to average latest 3 months percentage?
Simon Runc Jun 9, 2016 7:20 AM (in response to Mark Fraser)I'd forgotten about that...I just assume they always qualify...you will be missed (I'm, of course, referring to the Total Football....and not the 'Fans' in your link!!  how very Dutch!)
Hopefully we can make it far enough that penalities are even an option!! (not made it out of the group stages for a while!!)
Yes there is a real satisfaction to getting a table calc to sing & dance!!

7. Re: How to average latest 3 months percentage?
Leandro Nicolás Feb 6, 2018 8:26 AM (in response to Yew Seng Lai)It is not right to make an average out of percentages. At least not mathematically.
You have to sum the 3 months of Sales and then divide by the 3 months of Target.