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 -
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.
208522.twbx 13.2 KB
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..
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.
Thanks for the ping...I've only had a quick read through, and have come up with this formula
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 euro-2016 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
1 of 1 people found this helpful
Thank 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!!
That said, I will always support England, I enjoy the pain of disappointment and penalties too much!! haha
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!
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!!
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.