5 Replies Latest reply on Nov 6, 2017 2:15 PM by Joe Oppelt

    Using the excel equivalent of COUNTIFS in Tableau

    Eric Kolman

      Hello,

       

      I would like to know what I could do in Tableau to mimic the excel formula of COUNTIFS. I have tried to use IF CONTAINS but that is not giving me the numbers I am looking for.  To give some more background, we are analyzing survey response data. One of the questions asked is "What type of organization do you work in?". A user has the option to select one or many options for that question. So the raw data file looks like the following:

       

      User      Org Type

      user 1    Org Type 1

      user 2    Org Type 2

      user 3    Org Type 1, Org Type 2

      user 4    Org Type 3

      user 5    Org Type 1, Org Type 2, Org Type 3

      etc..

       

      User 3 indicated two different org types and user 5 indicated three different org types.

       

      I want to show a distinct list of Org Types and be able to apply some type of aggregation to each group (avg age for example)

       

      In Tableau, I created a calculated field called Org Type buckets with the following calculation:

       

       

      When I plot this on the Rows shelf, and throw avg(age) on the columns, I am getting the 13 org type options listed, but I am not getting the numbers I am looking for.  The numbers are correct for a few of the groupings but not all of them. (I initially calculated the numbers in excel using the countifs/averageifs formula).

       

      Any one have ideas on what is happening and how I can fix this?

       

      Eric

        • 1. Re: Using the excel equivalent of COUNTIFS in Tableau
          Joe Oppelt

          A multi-tiered if-elseif calc will stop when the first condition is met.

           

          That's because a calc can have only one value per row.

           

          Are you trying to determine that out of the 10 users, 8 chose value 1, and 4 chose value 2, and ... etc?  (So the sum of these counts can add up to more than the number of users because a user can select more than one value?)

          1 of 1 people found this helpful
          • 2. Re: Using the excel equivalent of COUNTIFS in Tableau
            Eric Kolman

            Hi Joe,

            Thanks for the quick reply. That now makes total sense why the values are not right.

             

            Yes that is essentially what I am trying to do. We would also like bring in a few different metrics (avg age, avg tenure, avg salary) to show how they differ by org type.

            • 3. Re: Using the excel equivalent of COUNTIFS in Tableau
              Jim Dehner

              Hi Eric

              I would really like to see your workbook on this - in a twbx format with the data to see the data and how your formula is working-

              it seems pretty complex but maybe that is needed

              With tableau if functions the calculation will process and If clause until it finds a true then it will apply the Then clause and move on to the next record

              If you data is structured like you show with a single record containing multiple values for org type tableau will not see them all -

              You reference an excel function - there is a fundamental difference between excel and tableau - excel is a cell based system where the calculation is done at the cell level and moves cell to cell

              Tableau is data base drive and calculations are applied to measures along dimensions - Think like and entire column in excel -

              So what - Tableau wants to see all your "Org type" data in a single column and your user information in another single column and yes that would result in 2 records for User 2 and 3 Records for user 5 - then you can use something like {fixed [org type] : countd(user)} to do your count  -

               

              The fixed statement in words would read for each org type count the distinct number of users  - (it will count user 2 and 5 in each of their org types0

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: Using the excel equivalent of COUNTIFS in Tableau
                Jim Dehner

                sorry Joe

                Didn't see you were already helping eric out

                Jim

                • 5. Re: Using the excel equivalent of COUNTIFS in Tableau
                  Joe Oppelt

                  Jim Dehner wrote:

                   

                  Hi Eric

                  ...

                  Tableau is data base drive and calculations are applied to measures along dimensions - Think like and entire column in excel -

                  So what - Tableau wants to see all your "Org type" data in a single column and your user information in another single column and yes that would result in 2 records for User 2 and 3 Records for user 5 - then you can use something like {fixed [org type] : countd(user)} to do your count -

                   

                  ...

                  Eric -- This is what my question was leading to. ^^

                   

                  If you cannot re-shape your data like this, then you can create in Tableau as many calcs as there are possible choices from the multi select list.  You can do it that way, but if there are 100 possible choices, it can get pretty cumbersome.