3 Replies Latest reply on Jan 4, 2019 3:12 PM by Ken Flerlage

# COUNTIF Based on Year and show the difference

In the Attached Spreadsheet I have date and feedback type, what I am trying to do is create a calculated field that will give me difference of count for year 2018 and 2017 is it possible to do in Tableau. I searched many posts and google but couldnt find anything.

• ###### 1. Re: COUNTIF Based on Year and show the difference

I'd start by creating calculated fields that give the counts for each year:

2017 Count

// Get the count for just 2017.

IF YEAR([Date])=2017 THEN

1

ELSE

0

END

2018 Count

// Get the count for just 2018.

IF YEAR([Date])=2018 THEN

1

ELSE

0

END

Then one that gets the difference:

Difference

// Difference between 2017 and 2018.

[2018 Count]-[2017 Count]

See attached.

If this helps to resolve your problem, then please be so kind as to mark this response as the "correct answer." This will close the thread and will make it easier for people to find the answer to similar questions in the future. Thanks!

• ###### 2. Re: COUNTIF Based on Year and show the difference

Awesome this works great, thank you so much Ken!!

I put the Feedback Type and Subtype in the Rows and put the SUM(Difference) in the Shape makes like shown here in screenshot. What I am trying to do is that the difference between 2017 and 2018 count if the value is negative then it should be down arrow shape, if value is positive then up arrrow shape and if zero then diamond shape, is this doable, coz right now it gives different values and more than three different shapes that i want to use

Thanks

Regards

Chintu....

• ###### 3. Re: COUNTIF Based on Year and show the difference

One way to do this is to put SUM(Difference) on the text card, then right click on that pill and choose "format". On the Pane tab, change the number format to custom and type▲;▼. This will give you an up arrow for positive numbers and a down arrow for negative.

See attached workbook.