Thank you for the detailed response. We were guessing that the issue is related to operations order, and sensing a conflict with the calculated field, but couldn't find it documented anywhere. Your explanation is the best I've seen yet.
To Tableau folks - would be great if we could find out a way to perform this. The frustrating thing with the missing grand totals is that all the numbers are right there on the screen - I can add them up myself with a pen and paper, but tableau refuses under certain circumstances.
Seems logical to me that Grand totals could calculate last, and without going back to the data source - just add the numbers in the table itself, more like a table calculation.
Anyway, thanks for all the other tips in this thread - some very cool ideas here.
Hi there all, first post here so apologise if this is something already discussed. I have understood the use of ranking to group my data into Top x and other. My question is that if I am using a heirarchy for products for example L1: Suitcases, L2: Roll Along, then item description, would it be possible to have the grouping of the results occur each time you expand down the heirarchy. So I expand TOP 10 at L1, this returns TOP 10 and other at L2 and so on.
Interested in your thoughts.
I am not sure I understand what you are looking for, can you please provide a sample data, and describe the interaction and result that you expect?
I've had great sucess creating Top N lists, in fact I created an internal document to help others in our organization create their own Top N lists. (Giving proper credit and links to this thead and the blog posts)
My question is regarding grouped fields. (We are still on v6.0 but hope to upgrade soon). When I create my calculated field for 'Customer' using this formula
IF [In top n?]
THEN ATTR([Customer Name])
I want to replace the 'Customer Name' with a group but my group is not in my list of fields. Is there any way to do this?
That is a limitation of the Ad Hoc Group field type. Ad Hoc Groups cannot be referenced in a calculated field's formula. You will need to recreate your group as a calculated field, you can sue a CASE statement to accomplish this like
CASE [Code] WHEN "A123" THEN "A" WHEN "A456" THEN "A" WHEN "754 B" THEN "B" WHEN "stuffBstuff" THEN "B" END
Then this calc field can be referenced by other calc fields.
Thanks Joe! This is going to be a huge CASE statement.
Possibly you could use data blending with a spreadsheet that holds the group mapping? (I think that should work but would need to try it to be sure.) Or add a lookup table to your datasource?
Richard - that's a good idea. I'll try that, then I can make a use case for moving this grouping in to our MDM DB where it should be.
It would not work across a data blend, because data pulled across a data blend must be aggregated, and therefore not a dimension that can be used for addressing (ie attached to), and can only be used for partitioning if not ignored in table calculations.
In this situation, you would need to use the result of this for the compute using and you cannot use an aggregated value for compute using, only for partitioning if you set it so.
It would work if you did the join before Tableau, or at the data connection, but this is only viable when you can add a table to your data source.
If you are using an extract, then you can materialize this CASE statement by optimizing the extract.
I happen to have the advantage of being able to do it at the data source. I can put a CASE statement there.
Thanks for all the help guys!
Sorry to the others who aren't able to do this at the data source.
Joe, Is it possible to change the formula to do a Count Distinct for a Top n?
I do not understand your question. Can you please provide a sample data set that represents your situation, and detail what you would expect for a result and what interaction you would like to allow for?
You created an example 'Top n RL with condense option' where the user can see the Top n in a given category. For instance, we have orders that were planned for a installation in a month but the installation didn't occur so the order is planned in the different month. I only want to count that order once because the order may move several times before the actual installation occur. When I look at the my raw data, the same order number appears multiple times but I only want to count it once. Using your example I would like to count distinct the order once.
Here is an example:
6100048944 Everett Jones May 01, 2011
6100048944 Everett Jones June 01, 2011
6100048944 Everett Jones July 01, 2011
When I count the # of orders for Everett Jones it should be ONE even if there are three different lines for the order 6100048944.
This thread is old, and I do not believe that the old solutions offered here are optimal.
A better method in my opinion of a dynamic top n is located at Re: Top n vs the rest
as for these latest questions from Lizz45ie and Matthew, in order for me to provide assistance, I need at least a sample data set that represents your situation. I am sorry, but three records does not represent a situation. If I am to provide assistance on dealing with data, I need data, not a description of data. This is mainly because the descriptions of data leave out details that may not seem important enough to mention in a description. I would prefer a packaged workbook because table calcs are not defined by just their formula, but what dimensions are in use on the worksheet and the compute using settings and many other factors.
Please provide a packaged workbook so I can understand your situation and provide assistance.
And it is okay to start a new thread. This thread is old, mixed, and out dated.
Thanks Joe. I'd love to send you a packaged workbook but am limited by HIPPA rules, my data contains protected health information so unless I deidentify all the data in the workbook I could find myself in court, subject to a fine for me and my organization as well as possible jail time.
As for my posts on this 'old' thread, I'm waiting for our 6.1 upgrade next week and am unable to opne the newer examples posted.