4 Replies Latest reply: Jul 17, 2011 10:07 AM by iwona1234 RSS

    Expression in pivot table.

      Hi,

      I have transactions table in data model:

       

      Year
      Value
      20071
      20072
      20093
      20104

       

      I build pivot table with one dimension(Year):

       

      Year
      Sum of Value
      20073
      20093
      20104

       

      I need to add new expression column where will be calculated sum of value of all transactions from transaction table till the end of the dimension column year:

      Year
      Sum of Value
      Sum all Values till end of year
      200733
      200936
      2010410

       

      I need to find the solution how build the expression(set analysis) to calculate this field. (I don't want to use the aggregated sums).

      I tried something like that:

      Sum(ALL{$<Year={"<=$([=Year])"}>} [Value])

      but it doesn't work. Could anyone help me in my completly first step in QV?

        • Expression in pivot table.

          Hi,

          You can try something like that

          if(RowNo()=1,[sum of value],[sum of value]+Above(some all values))

            • Expression in pivot table.

              Thank you for your help .

              It is the good solution, but based on the aggregated sums of columns above in pivot table.

              I want to avoid the calculations based on shown columns (it can be not fixed). I want to base my calculation on all data in data table, if it is possible.

                • Re: Expression in pivot table.
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  Are you using a straight table? If yes, go to the Expressions tab in the chart Properties, click on the expression and tick the "Full Accumulation" check at the bottom of the dialog.

                   

                  If you are using a pivot table instead, unless you allow the users to pivot the dimensions and expressions, the following should work

                   

                  RangeSum(Above(Sum(Value), 0, RowNo()))
                  

                   

                  The Above() refers to the position of the dimension to aggregate, not to the column name, so that expression will work and return the same as the "Full Accumulation" mentioned above.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

                    • Expression in pivot table.

                      Thanks a lot for your help, but this method of calculation gives me results which depend from views and filters. I need to have contant results calculated only from transaction table.

                       

                      Example situation:

                      When I click on row with 2010  I should have result:

                      Year
                      Sum of Value
                      Sum all Values till end of year
                      2010310

                       

                      but when I use your accumulation method I have:

                      Year
                      Sum of Value
                      Sum all Values till end of year
                      201034

                       

                      which is wrong value.

                       

                      Probably I should prepare this calculation during loading the script, but I'm looking for the solution to omit this.