6 Replies Latest reply: Jan 31, 2012 2:41 PM by Robert Szarek RSS

    Percentage of TOTAL Sales

    Robert Szarek

      Hi Experts,

       

      I have the following table:

       

      AGGR1 , AGGR2 ,    AGGR3, Amount

      Sales    , Busines1 , Product11, 10

      Sales    , Busines1 , Product12, 20

      Sales    , Busines2 , Product21, 20

      Sales    , Busines3 , Product31, 40

      Expenses, Expenses, Expenses, 400

      Promotion, Promotion, Promotion, 300

      OOI,            OOI,          OOI,        200

       

      I would like to build a QV table, showing me totals for AGGR1 not being Sales (this is easy), but as well showing me in respective rows, how much the given category is in relation to total Sales, so:

       

      In case of expenses I would like to see total for AGGR1 = Expenses (This I know how to get) and then in next collumn, this figure divided by TOTAL SALES. I do not know how in the row containing expenses, can I get the TOTAL SALES figure (denominator of my %)..

       

      Thanks a lot for your help

       

      //Robert

        • Percentage of TOTAL Sales
          jagan mohan rao appala

          Hi,

           

          Try this

           

          =Sum(Sales)/Sum(TOTAL Sales)

           

          Regards,

          Jagan.

            • Percentage of TOTAL Sales
              Robert Szarek

              Hi,

              Thanks for your answere, but this does not work... I was reading about Sum(TOTAL variable) construction and it does not fit to my need because:

               

              - referring to my example table, I do not want to calculate how much "Business1" is in relation to total "Sales"

              - I want to show how much the variable not being part of SALES (expenses for instance) are in relation to TOTAL
              SALES. I would like to build a cross tab with AGGR1 as first dimension, then

              sum ({$<[AGGR1] -= {'SALES'} > Amount}

              as second dimension and percentage of items not having in AGGR1 "Sales" to total sum of those items having in AGGR1 "Sales" as 3rd column of my table...

               

              Please, anyone has any idea?

                • Re: Percentage of TOTAL Sales
                  Stefan Wühl

                  Robert,

                   

                  not sure if you really mean that you want sum ({$<[AGGR1] -= {'SALES'} > Amount}

                  as second dimension, I think you need advanced aggregation then, like

                   

                  =aggr(sum ({$<[AGGR1] -= {'Sales'} >} Amount), AGGR1)

                   

                   

                  Anyway, I believe you can call the sales amount in any cell using dollar sign expansion, something like

                  =column(1) / $(=sum({<AGGR1= {Sales}>}Amount))

                   

                  See also attached.