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.
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.