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

    COUNTIF Based on Year and show the difference

    Chandresh Mehta

      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
          Ken Flerlage

          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
            Chandresh Mehta

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

             

            One more question if you can please help

             

            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

             

            Appreciate your time and help

             

            Thanks

             

            Regards

             

            Chintu....

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

              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.