
Re: Multiplying by a fix value located on a Pivot table
Stefan Wühl Nov 3, 2012 11:49 AM (in response to C V)The dimension values in your advanced aggregation will be sorted by load order, not by the sort order you define in the chart. So using top() function here will not return a correct result in general.
But you can ask for the maximum value, since this is what you want to get anyway,right?
=if(rowno()=0,
SUM(AGGR((Amount(Value*max(total<Category>aggr(SUM(Amount),Category,Class,Code))/1000))*Price,Type,Category,Class,Code))
,Column(6)*Column(7))
You can probably simplify this expression a bit further, but this one should return a correct result now.
Regards,
Stefan

Re: Multiplying by a fix value located on a Pivot table
C V Nov 3, 2012 2:41 PM (in response to Stefan Wühl )Hello Stefan,
Thanks for your fast reply. It was really close. Yes, you are right: I am looking for the maximum value on the field “Amount” regarding to the field “Class”. I tried your expression. The result for “Category B” fits correctly, but for some estrange reason for “Category A” don’t.
The correct sum for ”Category A” must be 21600 but it returns 21350.
Am I missing something?
Thanks for any idea.

Re: Multiplying by a fix value located on a Pivot table
Stefan Wühl Nov 3, 2012 3:16 PM (in response to C V)You can have multiple records per combination of your aggr() dimension values for field Amount, so you need to aggregate:
=if(rowno()=0,
SUM(AGGR((sum(Amount)(Value*max(total<Category>aggr(SUM(Amount),Category,Class,Code))/1000))*Price,Type,Category,Class,Code))
,Column(6)*Column(7))

Re: Multiplying by a fix value located on a Pivot table
C V Nov 3, 2012 5:21 PM (in response to Stefan Wühl )Thanks a lot.


