9 Replies Latest reply on Jan 8, 2016 10:19 AM by amardeep singh

    hierarchy ranking

    amardeep singh

      hi all

      i am having a issue with the dense ranking function

      i have a hierarchy defined as

      group group 1

      corp      ON

      corp       QC

       

      basically i am trying to rank it by viewers measure

      so when i dont expand the hierarchy it ranks fine...meaning

       

      group   rank       views

      corp        1          10

      corp         2           5

      corp         3          3

       

      but when i expand the group hierarchy, it breaks

       

      group  group1 rank views

      corp      ON       1      5

      corp      ON       2      3

      CORP    QC      5      5

       

      any idea why its happening

        • 1. Re: hierarchy ranking
          Gopi Chand

          Hello Amardeep Singh,

           

          Your example is confusing me.

          group   rank       views

          corp        1          10

          corp         2           5

          corp         3          3

           

          Above one will not happen in tableau since 'corp' will not repeat 3 times without other drill down dimension. (your example should aggregate by corp level)

          group     rank     views

          corp         1           18

           

          Please post a .twbx file to see what you are looking exactly.

           

           

          Regards,

          Gopi

          • 2. Re: hierarchy ranking
            amardeep singh

            hi gopi

            attached is the workbook

            as u can see that if i expand the pc hierarchy

            the qc ranking starts from 5

            • 3. Re: hierarchy ranking
              Joe Oppelt

              That needs to be a TWBX file, not a TWB.  Do FILE -->  "Export Packaged Workbook" to get the TWBX

              • 4. Re: hierarchy ranking
                Joe Oppelt

                Will this be a V9.0 file? 

                • 5. Re: hierarchy ranking
                  amardeep singh

                  attached is the twbx file

                  and  yes its version 9

                  • 6. Re: hierarchy ranking
                    Joe Oppelt

                    See attached.

                     

                    I added the value that you are ranking to the sheet so we can see what's happening in there.  (And I hid a few columns so we can see it easier.)

                     

                    When you expand, Tableau is doing exactly what you asked.  Rank them.  The values all change because of the expansion.

                     

                    But because of the order of "pills" on the ROWS shelf, Tableau is breaking them apart first by PC, then by ProvinceCode.  THEN it displays the ranks as computed for that ordering of dimensions.

                     

                    Now see Sheet 2.  I moved RANK to the front of the line.  It orders things differently, obeying the RANK values first.

                     

                    Now see Sheet 3.  I made a copy of the RANK calc.  If you want to see the RANK values AFTER the hierarchy, put a copy of the calc after ther hierarchy.  Then "Hide Header" on the first RANK calc in the front of the line of pills.  (And this is why I made a copy.  If you just use the same calc twice in the list of pills, when you hide the header on the first pill, it hides it for both occurrences of the calc.  But tableau sees the copy of the calc separately, and treats it separately.)

                    • 7. Re: hierarchy ranking
                      amardeep singh

                      thank you so much this is exactly what i was looking for. quick question in your sheet 3, coroprate gets repeated, is there a way to make it so it doesnt repeat just to make it like a cleaner version

                      • 8. Re: hierarchy ranking
                        Joe Oppelt

                        Technically, it's not getting repeated.  It only LOOKs like repetition because the first dimension -- RANK -- is hidden.


                        Do "show header" on RANK to see the rank numbers.  Scroll down to 979.  You will see that "Corporate" is not duplicated there because there are two "Corporate"s for that one rank value.  Now scroll down to 1012. There are a slew of rows tied at 1012 (DUR_RNK value = 0).  "Corporate" is listed only once there.

                         

                        Is eliminating the repetition crucial?  You might be able to do some sort of LOOKUP() looking at the prior row to see if it's the same as the current.  If it's the same, set to "", else set to [pc], and display that.  Having the hierarchy complicates this, and I don't have time to mess around with that.  I can only offer the suggestion.

                        • 9. Re: hierarchy ranking
                          amardeep singh

                          thats ok thank you so much for your help