topic PRice Index in QlikView

PRice Index

Fri, 04 Dec 2009 11:24:54 GMT

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?

PRice Index

Fri, 04 Dec 2009 15:02:34 GMT

Hi,

Could you attach an example data file?.

Best regards.

PRice Index

Fri, 04 Dec 2009 22:18:31 GMT

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

PRice Index

Tue, 08 Dec 2009 05:27:58 GMT

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%

PRice Index

Tue, 08 Dec 2009 05:46:42 GMT

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.