10 Replies Latest reply: Sep 8, 2016 12:19 PM by Juan Patricio Iorio RSS

    Sum, aggr and total

    Juan Patricio Iorio

      Hi everyone!

       

      I have the next data:

       

      type  transactions  client      type2

      1           3              name1     123

      2           4              name2     234

      3          11              name3     345

      1           4               name4    133

      ......

       

       

      There are a lot of types.

      I create a pivot table with the dimension type and, for each one, i created a calculated dimension:

       

      =IF(

            aggr(

                   rank(

                          SUM(

                                   {<

                                       type2 = {'1*'}

                                    >}

                                    transactions

                                  )

                           ),type, client) <= 10,

        client)

       

      this bring to me the top ten client with the maximun transactions (in the expression I sum the transactions where type2 = {'1*'})

      This is ok, but, i need another expression that sum all the transactions grouped by type (for the example data for the type 1 the sum is 7), i try with aggr, sum and total but it doesnt work.

       

       

      Thanks a lot!!

       

      Upload example!