4 Replies Latest reply on Oct 4, 2018 10:47 AM by Tony R

    Unique count across multiple columns- first instance only

    Tony R

      Hi, I’m brand new to Tableau and looking to get a unique count across multiple columns. I only want to count it once per Ticket number. I’m not able to post the workbook due to work computer security.

       

      I have tried pivoting the data, which worked but messes up other data due to it creating a bunch of extra rows to normalize the data.

       

      im creating a dashboard that will extract from excel document nightly as we add more data.

       

      I did a small mock up and attached What data looks like and desired outcome.

       

      any help I’ll be greatly appreciated

        • 1. Re: Unique count across multiple columns- first instance only
          meenu choudhary

          Hi Tony,

           

          Attachment missing.

          • 3. Re: Unique count across multiple columns- first instance only
            Jonathan Drummey

            Hi Tony,

             

            You have not attached any data, only as screenshot. For us to help you it's generally better to provide the data (even better is a Tableau packaged workbook with some sample data and your work so far).

             

            Please know that in general Tableau treats data like a database does where primarily aggregation is done over columns, whereas Excel with it's cell-based model doesn't really care whether a calculation is across columns or rows. In this case the data is in a "wide" format where the goal is to treat multiple columns as if they were a single Color attribute, and this is where it's helpful for Tableau to have that data as a single column.

             

            You have three-ish options:

             

            1) Leave the data as is and create a calculation for each color, it would be of the form:

             

            Blue:

            IF [Color 1] = 'Blue' OR [Color 2] = 'Blue' OR [Color 3] = 'Blue' THEN 1 ELSE 0 END

             

            And then aggregate that with the default SUM() aggregation. The challenge with this method is that you need a calculation for each color and any new color(s) will require additional calculations.

             

            2) Pivot the data and adjust your other calculations to deal with the "tall" data set, for example by using COUNTD() instead of COUNT(), and/or using Level of Detail expressions to adjust for replicated values.

             

            3) Alternatively you can have multiple Tableau data sources pointing at the original data. One would be an untransformed data set for some measures, and then a pivoted data set for the color calculation. Then worksheets can be built on each data source as needed and be combined into a dashboard if need be. If you want to show both the unpivoted & pivoted data in the same Tableau worksheet it can be possible to do this via a Tableau data blend however the extra complexity & performance implications could make suggestion #2 easier.

             

            Jonathan

            • 4. Re: Unique count across multiple columns- first instance only
              Tony R

              Thanks I’ll look at option 2