Multiplying by a fix value located on a Pivot table

[Anonymous]

Hello there,

I have a headache with this pivot table, so I will appreciate your help.

The correct value for Category “A” must be 21600 and for Category “B” must be 29700.

You are thinking that the solution is a simple aggregate function on the total. Believe me. But there is something else.

Two important things:

• The data are ordered “desc” considering the column named “Value”
• The Column named “Calculation1” has the complexity, because it takes the top value of the column “Amount” to multiply on its calculation.

Everything looks normal, until the sum on the subtotal of the column Profit is required.

The reason is simple:

            The column calculation1 has the expression:

            =Value*TOP(SUM(Amount))/1000

The TOP function was the only way that I found to select the maximum value of the column “Amount” according to the Dimension named “Class”.

But the TOP function doesn’t work properly on the column named “Profit” with the expression:

=if(rowno()=0,SUM(AGGR((Amount-(Value*TOP(SUM(Amount))/1000))*Price,Type,Category,Class,Code)),Column(6)*Column(7))

Any idea?

I try to develop a script to work with all the columns on a table without calculations. But when comes the TOP() function, I don’t know how to deal with a fix value on the script. 

I will appreciate a solution on the pivot table and another via script.

Thanks in advance,

[swuehl]

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

[Anonymous]

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.

[swuehl · Accepted solution]

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))

[Anonymous]

Thanks a lot.