7 Replies Latest reply on Feb 14, 2018 9:19 AM by Simon Runc

    Unique Grouping and overall Numbers.

    ross helliwell

      I have searched this to death but have no found a concise answer.

       

      In my example I have two columns of data and I need to count how many records have duplicates and the overall count.

       

      Eg.

       

      SID            Component

      ABC123     fg12

      ABC123     fg13

      DEF456     fg12

      DEF456     fg16

      DEF456     fg12

      DEF457     fg12

       

      In the above I need to list how many SID's are assigned to multiple Components.  I have got Tableau to list the duplicate records EG 2 SID's are assigned to multiple Components but I need the overall count for a summary dashboard.  In my detail sheet it shows:

       

      SID            Component

      ABC123     2

      DEF456     3

       

       

      But I need to be able to count the above rows eg 2 in a summary dash.   Any ideas how I can do this without have to construct  cleaver SQL to do it?

       

      Thanks

        • 1. Re: Unique Grouping and overall Numbers.
          Simon Runc

          hi Ross,

           

          So I think I get what you are after...let me know I've got it wrong!

           

          We can create a dimension for the SIDs using an LoD, which puts the distinct number of components against each SID

          [Component Count per SID]

          {FIXED [SID]: COUNTD([Component])}

           

          Once we have this we can use this to count them up (without needing SID or Component) in the Viz

           

          [Count of SIDs with more than 1 Component]

          COUNTD

          (

          IIF([Component Count per SID]>1,[SID],NULL)

          )

           

          Hope that helps

          • 2. Re: Unique Grouping and overall Numbers.
            ross helliwell

            Ah, you are a star.  That works perfectly.  I have come from a SQL server / VBA background and am having trouble trying to visualize the data.

             

            I do have anther similar question but based on a percentage threshold.  As soon as I figure out how to word it, I will post it and that will be my last problem to solve.

            • 3. Re: Unique Grouping and overall Numbers.
              Simon Runc

              Glad it did the trick...

               

              so FIXED LoDs (which this formula is) is like a sub query in SQL, joined back onto the data (at row level) on that dimension.

               

              So this one is doing

               

              SELECT COUNTD([Component])

              GROUP BY [SID]

               

              and then left joining this back onto the original data with SID as the join key. In fact it's a bit cleverer than that as is understands the Level of the Calculation, and adjusts it in the Viz, depending on the canvas (or Viz) LoD!

              • 4. Re: Unique Grouping and overall Numbers.
                ross helliwell

                So with this can it be expanded to 3 dimensions?

                 

                In a similar example:

                 

                SIDYesNoTotal%
                ABC12321333%
                ABC123561155%
                DEF456891753%
                DEF456971644%
                DEF45626875%

                 

                I can represent the above fine in tableau  where [Yes] and [No] are a measure count.  I can also filter on the percent calculation less than 50% to show 2 records.  How can I apply the above to show a count of 2?

                • 5. Re: Unique Grouping and overall Numbers.
                  Simon Runc

                  Yes pretty sure it can be.

                   

                  Can I just check. Are the Yes/No separate columns, or does the data go down the page? and would you calculate ABC123 over both rows (i.e. is the %age for ABC123 (5+2 = 7)/(3+11 = 14) = 50%), and if not is there another dimension (such as RowID) that let's us know that ABC123 has 2 sets of entries?

                   

                  If you let me know on that, I'm pretty sure we can find a way.

                  • 6. Re: Unique Grouping and overall Numbers.
                    ross helliwell

                    Yes it is in Columns

                     

                       

                    ComponentIDStandard IdHas Token
                    ABC123fg12Yes
                    ABC123fg13Yes
                    ABC123fg14No
                    DEF456fg12Yes
                    DEF456fg13Yes
                    DEF456fg14Yes
                    DEF456fg15Yes
                    DEF456fg16No

                     

                    This translates into the below:

                     

                        

                    Row LabelsNoYesGrand TotalNo %
                    ABC12312333%
                    DEF45614520%
                    Grand Total268

                     

                    So in the example above I need to show the percent of [Has Token] count per component ID which is easy enough in tableau but not the ability to show many may records it returned e.g 1 below 30%

                    • 7. Re: Unique Grouping and overall Numbers.
                      Simon Runc

                      hi Ross,

                       

                      So first we need to get the count for "No"

                       

                      [Has Tolken No]

                      IIF([Has Token] = 'No',1,NULL)

                       

                      and then we can use this to get the %age for each component...I've done this as an LoD, so the results are returned (as a real dimension at row level)

                      [%age No - LoD]

                      {FIXED [Component ID]: SUM([Has Tolken No])

                      /

                      SUM([Number of Records])}

                       

                      and then we can create the <=30% test...

                      [Count Distinct of ComponentID < 30%]

                      IIF([%age No - LoD]<=0.3,[Component ID],NULL)

                       

                      and then we can just bring this field in as a COUNTD (NULLs don't get counted in COUNTD....and every ComponentID over 30 equates to NULL)

                       

                      Hope that makes sense.