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.




      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?



        • 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]



          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:




                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


                    This translates into the below:



                    Row LabelsNoYesGrand TotalNo %
                    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.