1 Reply Latest reply on Jul 3, 2018 8:41 PM by swaroop.gantela

# Creating calculated for selected year

Howdy?  I have a problem regarding a calculated field in Tableau that I'm stuck on. Let me know if you can offer any help! Thank you!

I have a metric called "Rep %" and a dimension for "date" which has data from 2016-2018.  I want to use the Rep % for 2017 and times that by 10% and call it the over all goal for 2018.

Goal for Jan 2018 = 2017 Rep % * 1/12 * 10%; Feb 2018 = 2017 Rep % * 2/12 * 10%; Mar 2018 = 2018 Rep % 3/121 * 10%; April 2018 = 2017 Rep % * 4/12 * 10%

Rep % (actual) - metricRep % (goal) = Rep % for 2017 * 1.10 - calculated fieldDate - dimension
13.4%2016 - yearly value (only 1 value)
14.0%2017 - yearly value (only 1 value)
14.3%2018 - Jan
14.5%2018 - Feb

I have two questions:

1. How do you create a calculated field for the Rep % (goal) which only select the the 13.5% (2017 number) and times it by 1.1?

2. How do you create calculated field to show show this?  Goal for Jan 2018 = 2017 Rep % * 1/12 * 10%; Feb 2018 = 2017 Rep % * 2/12 * 10%; Mar 2018 = 2018 Rep % 3/121 * 10%; April 2018 = 2017 Rep % * 4/12 * 10%

This is the deal axis chart that I created, but it's not what I want because the goal number is incorrect.  I don't know how to only include/filter for 2017 data x 1.1 in the calculated field.

• ###### 1. Re: Creating calculated for selected year

Vu,

I'm not sure if I got there, but please see if the below could give ideas.

You could use a Level of Detail calculation to fix the 2017 Rep% across all rows using:

{ MIN(IF YEAR([Date Dim])=2017 THEN [Rep% Actual] END)}

Then you can calculation your monthly goals as:

IF YEAR([Date Dim])=2018

THEN [Target 2017]*1.1*(MONTH([Date Dim])/12)

END