1 2 3 4 94 Replies Latest reply on Jul 25, 2019 8:28 AM by Helen Sandwick Go to original post Branched to a new discussion.
• ###### 15. Re: Top N, with everyone else as "Other"

Brett,

My first thought would be to ensure that the partitioning is set properly for your table calc. Can provide a workbook with what you have so far?

• ###### 16. Re: Top N, with everyone else as "Other"

Your IF expression means that the sum will only be calculated for each of the top N rows, but it looks to me as if your WINDOW_SUM() is operating over the whole partition, and from what I remember of how this one worked all rows (i.e. the top N and the others) were in the same partition.  So it does look as if this will return the total including the others.  I'm pretty sure I recall that it had to be a single partition, because the classification of whether or not a row was in the top N depends on a table calculation and you can't partition by the results of another table calculation.

The window functions operate over a "window" onto the rows in the partition.  By default the window is the whole partition, unless you specify the optional range parameters.  From what I recall of how that original example worked, the rolled-up total of "Others" was just calculated for the first row beyond the top N, specifying a window from that row to the end of the partition.

In your case you want the opposite - you want a sum from the first to the Nth row in the partition.  This is a bit of a guess because I haven't seen the detail of your workbook, but if you want to return the total of the top N rows as a field in each of the top N rows, your formula probably needs to be something like this (I'm guessing that [Top ( ) Master Vendors] is a parameter for "N"):

If [Sort Order]=1 and [Rank]<= [Top ( ) Master Vendors]

then

WINDOW_SUM( sum([F_Net_AUD]), FIRST(), FIRST() + [Top ( ) Master Vendors])

end

• ###### 17. Re: Top N, with everyone else as "Other"

Thanks Joe & Richard. Yes Richard it is a parameter for n. I am trying to show the total of any given top n dynamically in another summary sheet in my dashboard. I have tried that formula  with + parameter but not with the double reference to first. Shall give it a crack and let you know. Failing that I'll post a copy of my workbook cheers

• ###### 18. Re: Top N, with everyone else as "Other"

Actually I think I was one out, I think it needs to be:

If [Sort Order]=1 and [Rank]<= [Top ( ) Master Vendors]

then

WINDOW_SUM( sum([F_Net_AUD]), FIRST(), FIRST() + [Top ( ) Master Vendors] - 1)

end

The reason for the references to FIRST() is that the window definition is relative to the current row.  The FIRST() function gives you the offset to the first row in the partition from the current row.  So if you want the window always to be the first 10 rows of the partition, your window needs to specify offsets 0..9 for the first row, -1..8 for the second and so on up to -9..0 for the 10th row.

• ###### 19. Re: Top N, with everyone else as "Other"

Thanks Richard i tried your calc but no joy. It seems to me that it should work  Attached workbook,  the calc is named Top master vendors many thanks Brett

1 of 1 people found this helpful
• ###### 20. Re: Top N, with everyone else as "Other"

Hi Brett

The problem is that the calculation has to work within the context of the correct partitioning.  The sheet you have the calculation on doesn't have the year or the individual vendors on it, so the calculation doesn't have the context it needs.

I haven't got time to explain fully now or put a proper demo together - it's not just a simple change to that sheet.  But you can easily demonstrate that your calculation itself is correct by dropping it on the Level of Detail shelf of the "Top And Others" sheet.  The tooltip will show the correct total for your top 5 vendors, since the calculation will inherit the correct partitioning definition from the other existing calculations on that sheet.  You can also include that calculation into the sheet title, for example.

Hopefully that gives you the clue you need.  If you're really lucky Joe may choose you as today's lucky winner of the free personalised Vimeo instructional video contest - Joe eats this sort of question for breakfast whereas it still takes the rest of us a while to get our heads round them.

• ###### 21. Re: Top N, with everyone else as "Other"

Cheers Richard thanks for your help I understand the issue now

• ###### 22. Re: Top N, with everyone else as "Other"

Here is your workbook modified to give the results I think you are looking for, and yes Richard, I did this while eating breakfast. :)

If this is the result you wanted, let me know and I'll see about making a video, because it is easier to show in a video than try to explain in a text comment.

2 of 2 people found this helpful
• ###### 23. Re: Top N, with everyone else as "Other"

Thanks Joe thats exactly what I was trying to achieve many thanks, Brett

• ###### 24. Re: Top N, with everyone else as "Other"

How would I convert Joe's last workbook to a stacked barplot with colors varying by rank? Is it possible?

• ###### 25. Re: Top N, with everyone else as "Other"

chrispope,

Lots of stuff is possible, but I am not sure what you are looking for. Can you please provide some sample data, or a packaged workbook with some sample up data that represents your situation, and mock up (or fully describe) what you want your end chart to show?

• ###### 26. Re: Top N, with everyone else as "Other"

This thread is awesome.  I have a related question: I have made a much simpler take on a top-N calculated field, which I am using as a dimension in a list.  The calculated field looks like this:

If Index()<=7

Then 'Top 7'

Else 'Not top 7'

End

I am using this as the first item on the rows shelf in a table.  However, I now have a problem using grand totals (and to some extent subtotals).  Whenever I try to use row grand totals, I get the following: "Cannot turn on grand totals for discrete measures".

I think I want my calculated field to be a dimension not a measure, but it won't budge.  Any idea how to get grand totals back when using a calculated field like this to break up a table?

Thanks

Mike

• ###### 27. Re: Top N, with everyone else as "Other"

Table calculations and the Grand Total/Sub-Total rows can create interesting situations. We only have limited control of the "Total" rows, but there are a few things that can be done in some situations that may get you the results you are looking for.

Since every situation is different, can you provide a sample workbook that represents your situation, not just a simplified version, something that represents the all dimensions that you want on your worksheet.

• ###### 28. Re: Top N, with everyone else as "Other"

Here's an example of exactly what we are trying to do.  We have a calculated field which is performing a segmentation into "top 7" and others, using the INDEX function.

What we want to do is switch on Row Grand Totals, but we get the error "Cannot turn on Grand Totals for discrete measures".

Any ideas?

Thanks a lot

Mike

• ###### 29. Re: Top N, with everyone else as "Other"

The combination of the table calculation, the INDEX() function, and the Grand Totals you are asking Tableau to add, does not fit with how Tableau evaluates Grand Totals and table calculations. I recommend you contact your Tableau sales/account representative and discus this feature request.

I agree that the error dialog could be written better, maybe something like: The result of a Table calculation will not segment Grand Totals or Sub Totals.

There may be other approaches to accomplish your end goal, such as custom SQL, or a dashboard with a layout container, but I do not believe it is currently possible with just table calculations and the Grand Totals on a single worksheet.

(Side note: here is my understanding of the situation: When you turn on Grand Totals, you are asking Tableau to run another query on your data source, at a different level of aggregation. That level of aggregation and 'group by' settings are determined by the dimensions you have on the worksheet. you can use calculated fields to turn a measure into a dimension, to get around the dial message sometimes, but a custom table calculation will always be stored in the Measures area, even though it says it is a dimension. This is because table calculations are evaluated after the initial queries to the data source, so the structure (partitioning and addressing) or the Grand Totals is determined before table calculations are evaluated. This is why you can alter the value within a Grand Total with a custom table calculation, but not effect what is returned for addressing when filtering on a table calculation and displaying a grand total. I look at it as an order of operations situation to be aware of when dealing with custom table calculations. I attached an example where you can turn the grand total off and on and because the filter is a table calculation you get a different display. [If you turn off "Row Grand Total", all the null rows will disappear. Those rows should be filtered out all the time, but are not because the table calc filter is not effecting the dimensions shown for the Grand Total {the dimension values used for addressing the Grand Total values to calculate}])

1 of 1 people found this helpful
1 2 3 4