5 Replies Latest reply on May 4, 2016 9:51 AM by Ivan Young

    Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)

    John Adubufuor

      Hi Everyone,

       

      I hope I am right to start a new discussion. I also replied to an existing one but saw many were advised to start a new one. I'll link them to each other to be safe.

      Re: Top N, with everyone else as "Other"

       

      I've hit a wall and am hoping someone can help me overcome this.

      I have Groups in my row and Dates (YrMo) in column. My end goal is to have a Top N with Other Groups where the ranking is based on the sum of records in the max date column only.

       

      What I find is the Top N is always ranking based on the row grand total... where as I need it on the max yrmo column alone.

       

      I've attached a sanitized workbook and screenshots to try and hope.

      I'm hoping someone with greater Tableau skill then I can help

      Thanks a tonne in advance

      ja

       

      P.S. In the other thread Joe Mako solved various set questions so I gave him a mention.

       

      This expanded view shows the Top 4 I actually want to return. Note the row total of the 5th > 4th

      Top_N_Group_By_Max_YrMo-Correct_When_Expanded.PNG

       

      This collapsed view shows the current yrmo 5th group Rccs in the Top 4. I want PtO) as above instead.

      Top_N_Group_By_Max_YrMo-Incorrect_When_Collapsed.pngJoe Mako

        • 1. Re: Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)
          Ivan Young

          Hi John,

          I think I've got a solution that will work for you but I'm not 9.2 and it looks like your workbook is a older.  Anyway it's pretty simple, although my first swing I ended up with the same result as you which I believe is top 4 overall not based on last month.  Below are my steps.

           

          Good luck,
          Ivan

           

          1.  Create max yr mo field. {Fixed : MAX([Yr Mo]) }

           

          2.  Create measure last month counts.  SUM(IF [Yr Mo] = [Max Yr Mo] THEN 1 ELSE 0 END)

           

          3.  Drag Group to filters.  Edit Filter and click on Top tab.

           

          4. Select Top 4 or use parameter by Last Month Counts and click ok.

           

          5. Take well deserved coffee break.  (hopefully).

           

          • 2. Re: Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)
            John Adubufuor

            Hi Ivan,

             

            Thank you for the quick reply.

             

            I was able to reproduce your approach and for a moment thought it was going to do it for me.

            The calculated last months count idea is useful as I'm now using it to limit my set to the Top N with Other successfully while viewing all data.

             

            Then I filtered a location and found it no longer returned the top N. I get the same issue with collapsable set as I do with the filtered group.

             

            At this point the only thing remaining is to be able to still see the Top N when I filter the data set by location.

             

            Any ideas?

            lmk

            ja

             

            All Data Collapsed Now Works (I See Top N + zzz-Other Groups)

             

            If I filter Location I expect to see these 4 + other (I expanded set to manually see top N)

             

            But Unfortunately I see this instead (Rccs is missing. Same results when I do not use set but filter Group as Ivan did)

             

            Here is v2 packaged workbook

            • 3. Re: Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)
              Ivan Young

              Hi John,

              I'm not entirely clear on what you are expecting.  The reason Rcss isn't showing up in your example is because it isn't in the Top 4.  Your top 4 is still GSS, TPed, Tlscs and PtOj.   If you go to  Analysis-Table Layout-Show Empty Rows, GSS will show up in your views.   Adding the location filter to context solve your problem but again I'm not sure what you are expecting when you select a location filter.  Let me know if adding the filter to context gives you your expected. Below is a link where one of the Tableau experts explains how TopN works.  Let me know if this helps.  There may be other ways to achieve your desired result.

               

              Ivan

               

              Top N Sets Not Working Correctly

              1 of 1 people found this helpful
              • 4. Re: Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)
                John Adubufuor

                Hi Ivan,

                 

                Adding the location filter to context did the trick. Well done sir!

                 

                i have never used that so your link really helped shed some light. I'm sorry I struggled to articulate the end goal clearly. To make matters worse I gave you the wrong screen when stating what I expected post filter :S

                Essentially the top 4 should always be based on the values in the max yrmo 'in the context' of the filters being applied. When Location = C1, then RCss, TPad, Fice, Ffce with 53, 18, 6, 3 in 201603

                 

                Thank you very much for the effort in finding a solution for me. This is only my 2nd time posting to find one but I can't count the number I've found simply searching these forums.

                Cheers

                ja

                 

                • 5. Re: Top N Group with Other Ranked By Max Date Group Record Counts (Not Total)
                  Ivan Young

                  Hi John,

                  I'm glad adding the filter to context worked.  I think your post was pretty clear and my assumption of what you were trying to do was correct I just wasn't 100% certain.  It was a pleasure working with you and finding a workable solution.

                   

                  Best,

                  Ivan