7 Replies Latest reply on Feb 18, 2016 9:10 AM by Yuriy Fal

    rank distorts calculation

    lawrence.prinsloo

      Hi all,

      I've attached a workbook with two sheets.  In the first sheet I have three columns:

      Downtime duration

      % Contribution to duration

      Accumulating the % duration (it grows to 100% for each pane)

       

      the second and third columns are calculated fields.

       

      In the second sheet, I've got the same columns as the first sheet but I've now included rank to order the downtime categories by largest to lowest.  It orders it correctly, except now the Accumulating % column (3rd one) doesn't add up correctly.

       

      Can anyone help on this?

       

      Thanks.

        • 1. Re: rank distorts calculation
          Yuriy Fal

          Hi Lawrence,

           

          I was quick and wrong with my first post, so I deleted it.

          Re-posting now the right way.

           

          Both RANK and Accumulating % are Table Calculations.

          They are unaware of each other (even on the same view).

           

          The RANK is "sorted" by definition, though Accumulating % is not.

          One could "sort" it properly in just two steps --

          both are done inside Edit Table Calculation dialog:

           

          Step 1 : Compute using --> Advanced

           

          with Rank - works - Step 1.png

           

          Step 2 : Restarting every --> Week of Start Time (the Pill just above DT Description in Addressing)

           

          with Rank - works - Step 2.png

           

          Hope it helps.

           

          Yours,

          Yuri

          • 2. Re: rank distorts calculation
            lawrence.prinsloo

            Thanks Yuri.

            This solves the problem I had with the table.  Ultimately I might need to put it into a chart (the table was the first step to ensure I was working with the correct data).  So if there's another hic-up I might return

             

            thanks again though - works the way I'd expect it too.

             

            Regards,
            Lawrence

            • 3. Re: rank distorts calculation
              Yuriy Fal

              Lawrence, you're welcome.

              • 4. Re: rank distorts calculation
                lawrence.prinsloo

                Hi Yuriy,

                I understand why it wasn't working the way I wanted it to and at least now I have the correct data but when I try put it into the chart (the real requirement), it breaks down again.

                 

                I've updated the file with the dual axis chart and colour coding.  I'm trying to do the following:

                y-axis 1 is the % of downtime contribution

                y-axis 2 is the totalising downtime % contribution (should add up to 100%).

                 

                The ranking is as per downtime contribution descending and finally, I want to colour code the downtime descriptions based on whether the accumulated sum is less than or more than 80%.

                 

                The two issues I still have are:

                1. The colour coding doesn't seem to use the data in the same order as the y-axis 1 and 2 sorting (I realise you said it's a table calc so they're independent but it seems overkill to have to do a more detailed calc in the calculated field)

                2. The y-axis 2 is selected as a line but it's a series of dots.  I appreciate that this could be because of ranking column I have but I've removed it from the headings - is there a better way to do this?

                 

                thanks for the input - this chart has taken me longer than I'd have expected it to, so your help's really been appreciated.

                 

                Regards,
                Lawrence

                • 5. Re: rank distorts calculation
                  Yuriy Fal

                  Hi Lawrence,

                   

                  You would like to set an "absolute" addressing & partitioning

                  for all Table Calcs on a view (there are 5 of them, actually).

                   

                  And you're right, it's sometimes cumbersome to check every Table Calc on a view

                  for the correct addressing & partitioning. But this is a part of "Why is Tableau doing that?"

                   

                  Fixing "Incorrect" Sorts with INDEX() | Tableau Software

                   

                  Please find the attached wb w/modifications.

                   

                  Yours,

                  Yuri

                   

                  PS  Sorry, I couldn't do better

                  with "connecting dots"

                  as on the Sheet 7.

                  1 of 1 people found this helpful
                  • 6. Re: rank distorts calculation
                    lawrence.prinsloo

                    Great thanks Yuriy.

                    • 7. Re: rank distorts calculation
                      Yuriy Fal

                      Lawrence, you're welcome.