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

# calculate percent of revenue goal achieved vs prior year revenue

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_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BOOKED_AMT BOOKED_AMT GOAL BOOKED * 1.05 GOAL BOOKED * 1.05 Difference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DT Difference 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 2012 2013 2012 2013 2012 2013 2012 2013 \$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_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BILL_DT BOOKED_AMT BOOKED_AMT GOAL BOOKED * 1.05 GOAL BOOKED * 1.05 Difference in GOAL BOOKED * 1.05 from the Last along Year of BILL_DT Difference 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 2012 2013 2012 2013 2012 2013 2012 2013 \$3,370 \$1,361 \$3,538 \$2,176 -62.00%