5 Replies Latest reply on Dec 11, 2017 9:41 AM by Hari Ankem

# How do I create a rank based on count per day?

I'm looking to rank each zone based on the count per day (or week/month) per zone and then find the avg rank for each zone based on the sum of the rank per day. I created a quick mock up in Excel, and also attached a packaged workbook.

So below in the first table, each zone has the number of records for that day. Then in the second table I did a rank based on the highest count being assigned a 1, second highest count being assigned a 2, and so on. Then for Avg Rank, I simply did a average of each zone's row. So Zone 5 with the 1.3 Avg Ranking is the worst zone, and so on.

How do I create this in Tableau? Also, is there a way to show the different in % when comparing one day to the previous day (for Zone 1, count on 12/1 compared to count on 11/30, etc)? Could this be shown in the same worksheet?

 11/30/2017 12/1/2017 12/2/2017 12/3/2017 12/4/2017 12/5/2017 12/6/2017 12/7/2017 Zone 1 0 44 86685 31 46 13 25 13 Zone 2 2 511 457 457 509 562 509 509 Zone 3 0 4263 803 803 3803 837 805 803 Zone 4 98 543 719 272 552 751 1341 769 Zone 5 3648 2428 1856 1331 3804 3439 2968 250 Zone 6 169 165 165 165 141 179 528 14 Zone 7 254 222 174 154 250 248 224 0 Zone 8 340 395 320 130 435 450 360 0 Zone 9 292 801 401 360 505 451 481 434 Zone 10 172 129 59 59 502 214 186 215 Zone 11 118 157 354 189 158 189 232 40 Rank: Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Avg Ranking Zone 1 10 11 1 11 11 11 11 9.4 Zone 2 9 5 5 3 4 4 5 5.0 Zone 3 10 1 3 2 2 2 3 3.3 Zone 4 8 4 4 5 3 3 2 4.1 Zone 5 1 2 2 1 1 1 1 1.3 Zone 6 6 8 10 7 10 10 4 7.9 Zone 7 4 7 9 8 8 7 9 7.4 Zone 8 2 6 8 9 7 6 7 6.4 Zone 9 3 3 6 4 5 5 6 4.6 Zone 10 5 10 11 10 6 8 10 8.6 Zone 11 7 9 7 6 9 9 8 7.9

Thx

• ###### 1. Re: How do I create a rank based on count per day?

Would something like this work? I have included multiple measures in the sheet, and you can remove what you do not need.

1 of 1 people found this helpful
• ###### 2. Re: How do I create a rank based on count per day?

Hari,

This works perfectly!

if you don't mind, can you walk me through what you did to create this fantastic sheet?

Thx a million,

Jeff

• ###### 3. Re: How do I create a rank based on count per day?

Sure.

1. Connected to the data source in Tableau.

2. Selected all the dates and did a pivot.

3. Renamed the Pivot Field Names to Date, and Pivot Field Values to Zone Value.

4. Added Date to the Columns and Zone to the rows, followed by the aggregate of Zone Value to the pane.

5. Right clicked on the SUM(Zone Value) and did a Rank quick table calculation. It was computed using Table (down).

6. Dragged the above quick table computed measure from the Text marks into the Measures and called it as Daily Rank.

7. Created a calculated field called Average Zone Rank as shown below.

8. Added the Average Zone Rank measure also onto the pane. The format can be modified as required to show the number of decimals. The order of the measures can be changed in the Measure Values section.

9. Added Zone Value to the Measure Values and performed a quick table calculation to show the difference.

Right click on the added measure and edit the alias as required.

10. Repeated above step to show the percent difference.

1 of 1 people found this helpful
• ###### 4. Re: How do I create a rank based on count per day?

Hari,

Apologize for the delay in getting back to you, but thx a million for the walk through as I greatly appreciate it.

Thx again!

• ###### 5. Re: How do I create a rank based on count per day?

You are welcome.