PRice Index

[#1]

I'm trying to develop a dynamic monthly price index chart. the basic expression would be:

sum( current average unit price / last year average unit price * spend / total spend)

=

sum( [Unit Price (USD)] / [2009_AUP] * [Spend Value (USD)] / sum (TOTAL $<MonthYear> [Spend Value (USD)]))

I keep getting no data or memory allocation errors... any ideas?

[#2]

Hi,

Could you attach an example data file?.

Best regards.

[#3]

I've attached my latest attempt in QV. Hopefully this makes sense. Below is how I've developed my calcs. I want to get to the "120% Weighted Index" figure below plotted in a monthly graph with the ability to filter and drill down by materials, plants, material classifications. So the calculations need to be done on the fly, but, ultimately need to be done at the Material & plant level.

Base AUP	AUP2	QTY	INDEX	WEIGHTED INDEX
5	1	5	20%	1%
4	4	1	100%	4%
3	3	0
3	3	3	100%	9%
5	3	2	60%	4%
4	3	5	75%	11%
5	4	5	80%	16%
1	3	4	300%	36%
5	3	5	60%	9%
1	2	5	200%	20%
1	3	1	300%	9%
2	1	0
	SPEND TOTAL	99
			120%	Weighted index

[#4]

Okay, here's another example. I've been able to get the annual total to work out properly, however, I want to see the same analysis in the monthly figures. In other words, if the year over year change is 80% (reduction of 20%), what was it in January. January should be something like 102%, February 70%, etc... see attached. example. Instead, I'm seeing the monthly percent of the total annual percent. ???

Month	Year	=AGGR(sum( [Unit Price]/ [2008 AUP] * [Total Spend] / sum(TOTAL [Unit Price] * Quantity)),Month)
		77.92%
1	2009	22.33%
2	2009	6.17%
3	2009	14.88%
4	2009	4.81%
5	2009	5.62%
6	2009	4.81%
7	2009	5.17%
8	2009	5.97%
9	2009	3.84%
10	2009	4.32%

[#5 · Accepted solution]

I think I may have answered my own question for once! Here's the formula that appears to give me the desired result:

=AGGR(sum( [Unit Price]/ [2008 AUP] * [Total Spend] / aggr(NODISTINCT sum([Unit Price] * Quantity),Month)),Month)

Gives me a monthly index of price change when comparing receipts to the previous years average unit price (calculated in the script) by part. Awesome! I also am able to obtain an annual total, though not in the same object. but, I'm cool wit dat.