Please provide a sample workbook (.twbx file) that demonstrates your data's structure.
I'm not sure I understand where you are running into trouble.
I put a rough attachment on it. What I really want is just the 2011 average. How can I get January 2012 rate/2011 average; February 2012 rate/2011 average;...;August 2013 rate/2011 average?
Can you repost as a packaged workbook (.twbx). A twb does not include the data. Thanks!
No problem--it happens often. I will take a look in a sec.
Curious--did the website warn you when you attached the TWB file? I ask because I thought they had implemented a warning to caution users about uploading TWB files on the site.
What do you mean when you say:
How can I get January 2012 rate/2011 average; February 2012 rate/2011 average;...;August 2013 rate/2011 average?
Please mock up the kind of view you want as and end-result, as I'm not sure I follow. I also don't two sets of prices, so I'm confused by your original question.
Sorry about the delay. I'm not really sure how to describe it. I just want to see the monthly rate changes relative to the average 2011 rate.
Id: January 2012 Rate/ average 2011 rate. February 2012 Rate/ average 2011 rate.....
I've attached an excel workbook that shows the desired column.
example.xlsx 10.5 KB
To do this, you'll need three calcs to do the following steps:
- Total Rate Per Year: Total(AVG([Rate])) This will be addressed by [Month]
- Lookup of Total for 2011: LOOKUP([Total Per Year]) This will be addressed by year, where you can set this to be relative to 2011 (which will lock it into only looking at the 2011 total avg)
- Weighted Rate: AVG([Rate])/[LOOKUP]
When defining the the table calcs for the [Lookup] please be aware of the top drop down of Edit Table Calculation dialog (one for the lookup itself and the other for the total), and the need for addressing the calculation for each layer separately as defined above.
I noticed the workbook was of an older version, but I built the example in 8.0. Hope this helps!
Example WKB.twbx 35.2 KB
This is GREAT!
Thanks so much for this detail. It's real useful!
I have another question. It's not really working for me because I don't actually have rates. I just have the spend/volume. Unfortunately, I think I will always run into aggregate errors due to this.
Do you have any advice?
Thanks so much again, that was a real useful explanation.
It should work out found. I would just substitute the AVG([Rate]) portion with your Rate formula (be it something like SUM(Spend)/Sum(Volume)) and it should work out fine. The set of formulas expect an aggregate as an input, so should work fine even if the formula aggregates the data first.
Thanks again for the help. I feel bad pushing for all these questions.
I'm still not able to get these into a proper chart. I would like to be able to show a line graph with these relative numbers.
Is that possible?