1 of 1 people found this helpful
Adam, I played around with this a bit the other day and couldn't come up with anything. Essentially you're needing to aggregate a table calculation that is of course already an aggregate. Maybe one of the other guys have a trick for doing this.
With the help of Jonathan Drummey, Shawn and I were taught a couple of ways this could be handled -- here is one (see attached version 8.2 TWBX):
One way of looking at your problem is that we cannot partition Table Calculations by the Results of Another Table Calculation. If we could get the data at a record/row-level, we could avoid some of the complexities of Table Calculations.
However, building off of what you have already done, we can get to the result you want dynamically by building our own Partitioning with an INDEX() calc. INDEX() is a Table Calculation that will return a value for each row in a partition (*see Table Calculation Functions for more details)
The partitioning of a Table Calc is based on the Dimensions in our view, and how we set our "Compute Using" -- Compute Using can be thought of as the "Partitioning" and "Addressing" of a Table Calc, where Dimensions used for partitioning create segments, or partitions, and the Dimensions used for "Addressing" are the rows within the partition (the "Addressing" rows, or the Row "addresses").
So, by building an Index calc, with a Compute Using on ID, we are effectively telling Tableau to Partition on NOTHING (as there are no other dimensions in the view, other than ID). You can see this by building an index calc, and looking at the Compute Using options for that Calc -- ID is the only dimension available. So, our Row "addresses" will represent one row for every ID in the view.
From here, we can lay out our data (see the BYO Index Calc in the attached to see how I've laid the data out, so I can visually inspect my results)
With the Index set to Compute Using ID (which again, means we are "Addressing" on ID, and nothing is partitioning the view since no other dimensions exist in the view), we now have a row-level value for each ID in the view:
From here, I have added my "Totals for Each Index Partition" calc, which is a calculated field of:
WHEN 1 THEN WINDOW_SUM(IF [Dynamic Population Quadrant Flag] == 1 THEN 1 END)
WHEN 2 THEN WINDOW_SUM(IF [Dynamic Population Quadrant Flag] == 2 THEN 1 END)
WHEN 3 THEN WINDOW_SUM(IF [Dynamic Population Quadrant Flag] == 3 THEN 1 END)
WHEN 4 THEN WINDOW_SUM(IF [Dynamic Population Quadrant Flag] == 4 THEN 1 END)
This calculation will sum the number of records for each Index partition; we have to identify the Quadrant flags within each Case statement, as otherwise, we would get the Total across all IDs (1500).
This calculated field has the same compute using, as do all the Table Calcs in this example (compute using, or "Addressing" on the ID field)
The result of this calc is shown in the BYO Index Calc view--we get the accurate totals for each of the first Four Index values,
From here, I duplicate the view (so all my compute using settings are kept in place, and locked in on the "ID" field in this example), place the Totals for Each Index Partition pill on the Label shelf, and the ID pill on the Detail shelf so it is available to the Calculations, but not showing up in the View itself.
The last step is to put a Continuous version of the Totals calc on the Filter shelf, and filter to keep only the "Special>>Non-Null Values".
For a good example of how the Table Calc gurus like Jonathan work something like this out using a Crosstab view (as we have done in this example), see the video "Setting up for Table Calculation Success" at this link: The specified item was not found.
The final view we are left with, after keeping Non-Null values, is below. I've highlighted the Status Bar, so you can visually see that we are left with only 4 marks in the view. That is the minimum number of marks required to create this view, and the Status Bar can be very helpful in understanding your results as you work through a problem like this one.
Thanks to Jonathan Drummey for helping to educate us all. There are certainly other methods for obtaining this same result; Jonathan mentioned a self data-blend solution as well.
I hope this helps. Please let me know what makes sense and what doesn't, as I've written this all out to sort-of help myself learn a bit in the process.
I was lost until Jonathan made it clear; all credit goes to him!
Thank you all so much, you are tableau wizards.
No problem. Feel free to ask any questions you might have. Cheers
Would someone be able to post this solution in v8.1. I have something similar that i'm working on.
Remie you can do it yourself. Download the workbook. Unzip it using any zip utility. Open the twb file in Notepad++ or other text editor, and change all the 8.3 versions to 8.1. There are three of them in the first 19 line of code. Now you can open it in 8.1
You missed the first step. You need to use WinZip or 7Zip to unpackage the packaged workbook. this will open you the workbook and all the folders and data inside. Packaged workbook are really just zip files with a different extension.
Then open the unpackaged twb file in Notepad++ and you'll be able to read it, and change it.
Thanks Shawn, have replaced them with 8.1 now. However when I open in Tableau I get an error saying Invalid database name value.
Sorry dude. It looks like you're out of luck. I wasn't able to hack it back to 8.1 either. Probably it's using one of the newer 8.2 features. So it looks like you're just going to have to upgrade.
My company has decided to stick with 8.1 for the time being so having to work around that.
I have put in a calculated field in which now labels which quadrant each account is in. Would appreciate if someone could show me how to get a separate table which shows the count of each quadrant like in the solution given above.
Quadrant Test.twbx 34.8 KB