11 Replies Latest reply on Nov 14, 2013 11:13 AM by Joey Chadwick

# I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

More detail:

I have two sets of prices that I'm trying to compare from January 2012 to August 2013. To standardize the two I'd like to take the average price in 2011; then divide each month's price by 2011's average.

I would theoretically like to write:

Rate/'2011 Rate'. and let it calculate each month's rate relative to 2011's average.

How do I get 2011 rate to just stay as the constant through?

Thanks,

Joey

• ###### 1. Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

• ###### 2. Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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?

• ###### 3. Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

Can you repost as a packaged workbook (.twbx).  A twb does not include the data.  Thanks!

• ###### 4. Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

There you go. Sorry about that.

• ###### 5. Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

Thanks!

• ###### 6. Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

• ###### 7. Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

• ###### 8. Re: Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

Hi Joey,

To do this, you'll need three calcs to do the following steps:

1. Total Rate Per Year: Total(AVG([Rate])) This will be addressed by [Month]
2. 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)
3. 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!

• ###### 9. Re: Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

Thanks so much again, that was a real useful explanation.

• ###### 10. Re: Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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.

• ###### 11. Re: Re: Re: I'm comparing prices over a monthly period but would like to use an annual average as the base. How can I do this?

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?

thanks,

Joey