9 Replies Latest reply: Jul 11, 2011 2:42 PM by John Witherspoon RSS

    Subtotal of subtotal....with a twist

      Gang - need your expertise on this issue. I need to have each of my "Incumbent Class" segments add up to 100%. As of now, the math divides it up accross the entire field - which i dont want.

       

      'New Work', 'Retain', and 'Share Growth' are all text attributes within my data. Since this is text within the field, i'm having trouble pulling out this calculation as it relates to the example below.

       

      What do you recomment?

       

       

      Current State

      Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
      ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
      WON$13,565,943.-$9,888,327.-$9,656,199.-$10,323,343.-
      LOST$5,569,704.-$5,545,557.-$8,139,290.-$7,256,197.-
      Total$9,945,184.-$8,259,788.-$9,002,761.-$9,026,600.-

       

       

      Desired State

      Incumbent_Class"New Work""New Work""Retain""Retain""Share Growth""Share Growth"TotalTotal
      ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
      WON$13,565,943.71%$9,888,327.64%$9,656,199.54%$10,323,343.59%
      LOST$5,569,704.29%$5,545,557.36%$8,139,290.46%$7,256,197.41%
      Total$19,135,647.100%$15,433,883.100%$17,795,489.100%$17,579,540.100%

       

        • Re: Subtotal of subtotal....with a twist

          Hi, recently i experienced something like that.

           

          What i did is:

           

          Create a variable like TableName.TotalAnnulizedRev = Sum(TableName.AnnulizedRev)

           

          Then in the chart the expression should be: Sum(TableName.AnnulizedRev) / TableName.TotalAnnulizedRev

           

          And them configure this expression to show data in percentage style.

           

          I'll hope this help you.

           

          Cya.

          • Re: Subtotal of subtotal....with a twist
            John Witherspoon

            Something like this?

             

            sum(Revenue)/sum(total <Incumbent_Class> Revenue)

              • Re: Subtotal of subtotal....with a twist

                Woo Hoo! Worked Great, thanks.

                • Re: Subtotal of subtotal....with a twist

                  John, one last question and this will close it out. For the "Annualized Revenue," this seems to be throwing me off. Basically I want to take the months of the contract and divide that over the total revenue. In short, "Eval Revenue / Duration (months)" using these Incumbent Classes. Here's what i have...the totals dont add up....wierd.

                  Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
                  ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
                  WON#######21%$9,888,327.17%$9,656,199.12%$10,323,343.14%
                  PENDING$6,907,328.72%$7,992,175.77%$7,967,419.81%$7,813,350.79%
                  LOST$5,569,704.7%$5,545,557.6%$8,139,290.7%$7,256,197.7%
                  Total$7,557,634.100%$8,051,235.100%$8,145,808.100%$8,034,882.100%

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  =SUM([Eval Revenue]), SUM([Eval Revenue])/div(SUM([Duration (months)]),12))

                    • Re: Subtotal of subtotal....with a twist
                      John Witherspoon

                      I don't really follow, but I suspect you want something more like this:

                       

                      12*sum([Eval Revenue]/[Duration (months)])

                       

                      Because (A+B)/(C+D) =/= A/C + B/D, and I think you want the latter.

                        • Re: Subtotal of subtotal....with a twist

                          Close, let me to a better job of framing it up....

                           

                          Annualized is just the value of the sale / contract years. For example a $12,000 sale over 2 years will have an annualized value of $6,000. My trouble is with the incument class again. How can i pull through only the sales that are categorized like the table below in an annualized format? PS: The 'totals' dont jive either.

                           

                          Thanks John!

                           

                           

                           

                          Incumbent_ClassNew WorkRetainShare GrowthTotal
                          ResultAnnualized RevAnnualized RevAnnualized RevAnnualized Rev
                          WON$13,565,943.$9,888,327.$9,656,199.$10,323,343.
                          PENDING$6,907,328.$7,992,175.$7,967,419.$7,813,350.
                          LOST$5,569,704.$5,545,557.$8,139,290.$7,256,197.
                          Total$7,557,634.$8,051,235.$8,145,808.$8,034,882.
                            • Re: Subtotal of subtotal....with a twist
                              John Witherspoon

                              I still don't follow, and to the extent that I do, my expression still looks right to me.  Taking your example, you have a $12,000 sale over 2 years.  2 years is 24 months, so I assume field [Duration (months)] has 24 in it.  And therefore, if this is the only sale, my expression gives you 12 * $12,000 / 24 = $6,000 as you requested.  What I was trying to say with my post is what do you do if you have a second sale, this one for $6,000 over 3 years?  That one sale is annualized to $2,000.  So is the total supposed to be $8,000 annualized?  If so, then the expression I gave gives that number, 12 * (12000/24 + 6000/36) = 8000.  If you want this split out by incumbent class, and incumbent class is a dimension, then it should split it by incumbent class. 

                               

                              I can't make any sense of your table without underlying data, particularly when you tell me that the totals aren't right.  It's just random numbers to me.

                                • Re: Subtotal of subtotal....with a twist

                                  Hopefully this helps - here's an example of the raw data and the revised table.

                                   

                                  Regards,

                                  Brian

                                   

                                   

                                  ResultsIncumbent ClassValueAnnualizedDuration (Month)
                                  WONNew Work $       954 $          954 12
                                  LOSTRetain $       985 $          493 24
                                  WONNew Work $     1,000 $          200 60
                                  LOSTRetain $       400 $          200 24
                                  WONRetain $       332 $          166 24
                                  WONNew Work $       100 $          100 12
                                  LOSTShare Growth $       343 $            86 48
                                  WONShare Growth $       234 $            78 36
                                  LOSTNew Work $       343 $            69 60
                                  WONShare Growth $         45 $            45 12

                                   

                                  Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
                                  ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
                                  WON$1,254.95%$166.19%$123.59%$1,543.65%
                                  LOST$69.5%$693.81%$86.41%$848.35%
                                  Total$1,323.100%$859.100%$209.100%$2,391.100%