
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PRice Index
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?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Could you attach an example data file?.
Best regards.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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% |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
