4 Replies Latest reply: Nov 3, 2012 5:21 PM by C V RSS

    Multiplying by a fix value located on a Pivot table

    C V

      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,