3 Replies Latest reply on Nov 3, 2016 8:55 AM by Shinichiro Murakami

Dynamic Top N based on calculation

Hello Tableau world!

I have a question. I have a data set with Case IDs and duration of cases. I want to filter the number of case IDs to 90% - e.g. CountD(Case IDs) *.9 which gives me my N number. I created a Set with the specific number for this month, but it would be AWESOME if the Top N number changes as the number of Case IDs increase or decrease.

Also, we are a bit out of touch using the 8.2 version....

• 2. Re: Dynamic Top N based on calculation

Thanks for your reply! I'm not sure how this relates to my issue...

let me provide an example:

Case ID    Duration

1234          2

2345          2

3456          3

4567          4

5678          8

6789         12

7891         15

8912         18

9123         21

1235         35

CountD(Case ID) = 10

Avg(Duration) = 12

I only want 90% Bottom (lowest duration) of the Case IDs  - in this case 9. I also only want the average duration of those case IDs which is 9.4

• 3. Re: Dynamic Top N based on calculation

Little bit tricky to calculate with "rank".

[Rank]

rank_unique(sum([Duration]),'desc')

[Filter N %]

if [Rank]/attr({fixed:countd([Case ID])}) <=(100-[Bottom N%])/100 then "hide" else "show" end

[Running AVG only Show]

RUNNING_AVG(if [Filter N %]="show" then sum([Duration]) end)

[Filter Last]

last ()