5 Replies Latest reply on Aug 14, 2019 1:34 PM by Soumitra Godbole

# Calculated field based on dimension value

Hello all,

I work with Tableau version 2018.3.

I have a sample data attached look like this :

I'm trying to do a COUNTD(Employee ID) based on the Year value, and then create a calculated field to as COUNTD(Employee ID) of 2019 -  COUNTD(Employee ID) of 2018.

I have seen a few posts on this subject with sum(), but it doesn't seem to work in my case.

Could anyone help on the exact script for the calculated field?

Thanks so much for the help.

Best,

• ###### 1. Re: Calculated field based on dimension value

Try this,

1 of 1 people found this helpful
• ###### 2. Re: Calculated field based on dimension value

Hi Christian,

As per your question and the sample data provided in the table, is the result 4 (7-3) ?

Also since year is already given in the data table provided by you, I wish to make a slight change to the proposed  solution which should be

This will correctly give us 4 (New employees in 2019 compared to 2018)

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 3. Re: Calculated field based on dimension value

Hi Soumitra,

Thanks for the response, the solution works.

However, is it possible to display the result with the records of two years in the same table?  as illustrated below?

Thanks,

1 of 1 people found this helpful
• ###### 4. Re: Calculated field based on dimension value

Sure it is possible. You will have to use the LOD { } outside the Countd so it will be independent of any year value

Here is a screenshot of what I have

Also note that No of Employees is nothing but  COUNTD ( [Employee ID] )

and a calculated field New Employees has been added which only displays the result for 2019 and nothing in 2018.

Let me know if this works for you. Thanks !

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 5. Re: Calculated field based on dimension value

Here is another way you can view the result (closer to what you requested)

Here there are 4 new fields including a dummy text field (value "Count of Employees") ie 2018 & 2019. The Delta field is nothing but the Difference Field (used above but renamed). Anyway I have listed the 3 fields with formulas below. This should give you the desired result.

1) 2018

2) 2019

3) Delta

4) Calculation