0 Replies Latest reply on May 24, 2013 8:48 AM by Vincent Atwood

    calculate percent of revenue goal achieved vs prior year revenue

    Vincent Atwood

      I am trying to figure out how to calclate a revenue goal percentage by Year, Quarter and Month to Date.  The goal is 5% over last year-to-date amounts.

       

      I have a date filed "[BILL_DT]", a revenue field "[BOOKED_AMT]", and a calculated Goal field named "[GOAL BOOKED * 1.05]" with the calculation: ( [BOOKED_AMT]*1.05 )

       

      Here's an attempt at an explanation - but it might be eaiser to jump to the screen shots below (also attached file) which shows the results I'm seeing, and the results I'd like to see.

       

      If I put BILL_DT in Columns, and BOOKED_AMT and [GOAL BOOKED * 1.05] as Measur Values:

      - the Goal actually shows up in 2012 ($3,538), this should actually be the 2013 Goal. 

      - the 2013 Goal ($1,430) is actually the booked_amt for 2013 * 1.05, which I don't want - that's actually just giving me the current year's revenu plus 5 percent.

      - the 'Difference in GOAL BOOKED number shows up as $2018 (regarless of the Relative to.. postion I select) which is not what I'm looking for.  It's subtracting the 2012 GOAL BOOKED value from the 2013 GOAL BOOKED value.  I need this years GOAL ($3,538) minus this years BOOKED ($1,361), which should be -$2,176 (letting the sales rep know he/she has $2,176 to make up by year's end in order to meet goal).

      - the % Difference is also not what I'm looking for.  It's calculating 2013 BOOKED vs. 2013 GOAL. I want to calculate 2013 BOOKED vs. 2012 GOAL

      which would give me -62%, not -59.99%

       

      What I'm lookin for is the GOAL BOOKED value for this year (in this case it's located in 2012 column - $3,538) minus this years 2013 BOOKED_AMT ($1,361).  The result should actually be -$2,176

      This is what I get as currently set up in Tableau:

      BILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DT
      BOOKED_AMTBOOKED_AMTGOAL BOOKED * 1.05GOAL BOOKED * 1.05Difference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DTDifference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DT% Difference in GOAL BOOKED * 1.05 from the Previous along Year of BILL_DT% Difference in GOAL BOOKED * 1.05 from the Previous along Year of BILL_DT
      20122013201220132012201320122013
      $3,370 $1,361 $3,538 $1,430 $2,108 $0 -59.59%

       

      This is an example of what I'm trying to produce:

      BILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DTBILL_DT
      BOOKED_AMTBOOKED_AMTGOAL BOOKED * 1.05GOAL BOOKED * 1.05Difference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DTDifference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DT% Difference in GOAL BOOKED * 1.05 from the Previous along Year of BILL_DT% Difference in GOAL BOOKED * 1.05 from the Previous along Year of BILL_DT
      20122013201220132012201320122013
      $3,370 $1,361 $3,538 $2,176 -62.00%