2 Replies Latest reply on Dec 24, 2012 9:30 AM by Atkinson Nigel

    Count IF...THEN?

    Atkinson Nigel

      Hi

      I have a simple dataset comparing nutrients in organic v conventional fruits and vegetables

      (first draft viz: http://public.tableausoftware.com/views/CvV/Sheet1?:embed=y).

      I've been playing with some calculations such as % and difference in absolute values. I managed them ok using a calculated field. What I need now is to count the number of times conventional > organic (and vice versa). This presumably involves IF...THEN, but I'm not clear about how to produce a number that I can include is a dashboard.

      Any help would be appreciated. Thanks.

        • 1. Re: Count IF...THEN?
          Joshua Milligan

          You are on the right track.  You might try some calculations like I've included below.  The first two are row level calculations, meaning that a result is calculated for each row of data.  The resulting calculated field can then be included in any visualization as an aggregate (like SUM).

           

          The third calculation is an aggregate calculation which means that the aggregation (based on the level of detail in the view) occurs first and then the results are calculated.

           

          You might play around with variations to see what yields the results you want.  I've uploaded your workbook with the calculations included.

           

           

          //#1: Count of Conventional Over Organic

          IF [Conventional] - [Organic] > 0 THEN [Conventional] - [Organic] ELSE 0 END

           

          //#2: Count of Organic Over Conventional

          IF [Organic] - [Conventional] > 0 THEN [Organic] - [Conventional] ELSE 0 END

           

          //#3: More Organic or Conventional?

          IF SUM([Organic]) > SUM([Conventional])

              THEN "More Organic"

          ELSEIF SUM([Conventional]) > SUM([Organic])

              THEN "More Conventional"

          ELSE "Same Number of Organic and Conventional"

          END

          • 2. Re: Count IF...THEN?
            Atkinson Nigel

            Thanks, that was really helpful. I was able to make progress with:

            IF [Conventional] - [Organic] > 0 THEN 1 ELSE 0 END

            C < O and O < C here: http://public.tableausoftware.com/views/CvV/CO?:embed=y

            This got me close to where I want to be graphically. In an ideal world I'd like to display a single number for e.g. C > O in a dashboard. I'll have a look at that after Christmas!

            Thanks again.