Keeping fixed value regardless the drill

[#1]

Hi to all,

I'm facing an issue regarding the association of data in my pivot table.

I'm displaying the average price for each material split up by months.

I use this calculation to get my average price: avg(TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE)) and it works if I show up all the months. When I click on one single month, I get an incorrect value. Normally, the average of all months is the same and I wouldn't expect to change although I click on one single month.

How can I do in order to keep the value fixed regardless the month I click on?

Thanks

Cristina

[bruno_m_santos]

I suggest to ignore the month in your setAnalysis expression.

Something like this:

=avg(TOTAL {$<Month=, MAT_MATERIAL_CODE>} aggr(PYprice, MAT_MATERIAL_CODE))

Hope it helps

[rustyfishbones]

If you are using Set Analysis

here is an example were we want the sum of the quantity

It will not change if Date, Country or ID Number is selected, you need something similar I think

[jonathandienst]

Hi

I suspect that you need to include the month in the aggregation:

=avg(TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE, month))

or

=avg(TOTAL <MAT_MATERIAL_CODE, month> aggr(PYprice, MAT_MATERIAL_CODE, month))

HTH

Jonathan

Edit:

I think that I misunderstood your requirement - to override the month selection:

=avg({<month=>} TOTAL <MAT_MATERIAL_CODE> aggr(PYprice, MAT_MATERIAL_CODE))

Jonathan

[#5]

Thanks for your suggestion, I've tried to apply it but unfortunately it doesn't work. The values still change

[#6]

Including the Month in the aggregation I get wrong values. The correct formula is that specified above.

[#7]

Please, find attached my application

[bruno_m_santos]

f you use setanalysis, you can ignore your selections.

[rustyfishbones]

Your file won't open

[#10]

I don't understand why my file can't be open. My uploaded applications are never able to be open.

Am I doing something wrong?