3 Replies Latest reply on Jun 18, 2018 1:49 PM by Mohammad Hassanpour

    Rank table calculation not behaving as expected

    Mohammad Hassanpour

      In this exercise, we are going to use table calculations to determine how we could use Tableau to dynamically recreate the ranks stored in the Rank by DogID and Rank by UserID variables. These variables, remember, chronologically rank each test according to its associated time stamp in Created At, and restart the ranking either for every dog or for every customer. To begin, make a group of Dog IDs that only has a few Dog IDs included (randomly choose 4 or 5 DogIDs). Use your new grouped variable and the filter shelf to ensure that you only analyze the data from the few dogs you chose in your workspace. This will make troubleshooting your calculations much faster.

       

      Next, place Dog ID on the rows shelf, followed by Test Name on the right-hand side. Then put Created At on Text, and adjust the variable aggregation so that you get a level of detail that is appropriate for the purpose of the exercise. You should see a table with Dog ID in the left-most column, Test Name in the column to the right of that, and the time the test was created in the right-most column.

       

      Our goal is going to be to create a column between the Test Name and Created At columns that uses a calculation (NOT Rank by DogID – that would be cheating!) to indicate the ranked order each test was completed, sorted from 1 to the last test taken. To achieve this, make a calculation that starts with RANK, and then drag the Created At pill from your workspace into the parentheses Tableau automatically inserts in your calculated field. Notice that Tableau will likely insert a date function with the level of detail you specified on the Created At pill in your table. You will need to choose whether the rank should be ascending or descending.

       

      As you troubleshoot your calculation, think carefully about whether the variables in your calculation need to be aggregated, and if so, how. Also think about what level of detail of the time stamps provided in Created At would be most useful.

       

      Once you have a valid calculation, drag it to Details. Then right-click (control-click) to edit the calculation, and navigate to the “Compute Using” screen. Chose the options you think are most appropriate for your goals. Once selected, convert the pill to a dimension and place it in the appropriate place on the Rows shelf. Assess whether your calculation succeeded by comparing your rank to the rank provided in Rank by DogID.

       

      Below I have a capture of the worksheet I have. I cant get the time to show properly, but that's not the issue I'm asking about.  You might have to right click and view data to see the actual times. The correct Rank by DogID, that provided is Rank by DogID.  My failed attempt is rankDogID_MH.

       

      Jonathan Drummey  I've read a lot of your posts. Very well written. You should write a beginners book for those of us that, I'm sorry to say, are struggling with and really hating Tableau so far.  Can you help me with this?

       

      My table calc, part 1:

      I'm want the rank by created at. I want the order the tests were completed by the dog. They must be short tests because more than one is often taken in an hour.  That's why I chose minute.

      Part 2:

      I thought all the dimensions in the view should show up in either partitioning or addressing. There seem to be a lot of dimensions in partitioning that are not in my view. I would think all I need in partitioning would be DogID and all I need in Addressing would be Test Name. I want to restart the ranking at every DogID, group by DogID, and have one rank for every combination of DogID and Test name.

        • 1. Re: Rank table calculation not behaving as expected
          Shinichiro Murakami

          HI Mohammad,

           

          Not 100% sure, but probably this helps?
          Yes, it takes time to learn table calc, and actually not easy to manage even after learning.

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Rank table calculation not behaving as expected
            Simon Runc

            hi Mohammad,

             

            so answering from your query in the other thread

             

            So I think we can achieve this using the following Table Calculation set up, which follows from my general "rule" on Addressing and Partitioning

             

            " ...I think of anything that is ticked as "run the calculation along this" (aka compute using/addressing) and anything as not ticked as "start the calculation again for every one of these" (aka partitioning)"

             

            In your example, we want the RANK to run over (address) Each Test Name and Create At (TRUNCed to minute) and then Restart the RANK every-time it gets to a new DogID

             

             

            There are times when you do need to use the "at the level" and manually use the "restarting every" (this is generally where the RANK LoD and VizLoD differ...but are quite advanced options, and 95% of the time I never need these)

             

            With regards Part 2...

            Not sure where your screen shot came from, as this looks like the old Table Calculation editor (from pre T10), and even then I don't understand how "Rank By Dog ID" could be in there, as we can't (much to several people, including Jonathan's frustration!) partition over Table Calculations. In general, yes every dimension in the vizLoD (viz Level of Detail) should be there as a partition option, and no others.

             

            Hope that helps, and if you can explain where the image for part 2 came from...happy to take a look

            1 of 1 people found this helpful
            • 3. Re: Rank table calculation not behaving as expected
              Mohammad Hassanpour

              I ended up fixing the issue on my own.  It looked like the calculation in the measures list was not matching what was in the rows shelf.  Once I corrected everything it seemed to work.

               

              I not to that screen on Tableau Public, but I cant recall how I got there. Sorry.

               

              Thank you for your help.

               

              Mo