
1. Re: Counting Rows With Table Calcs
Joe Oppelt Apr 30, 2018 9:55 AM (in response to Jonathan Hodge)Here's what's happening. A table calc filter does not remove rows from the table. It just controls what part of the table gets displayed on the sheet.
So index values 1 and 5 are still in the table. Tableau is telling you the index values of the rows that the filter dictates should be displayed.
So you need to have a counting calc that takes into account whatever is happening with the table calc filter.In the attached I made a calc ([Calculation1]) that excludes NULL for [Rank by Contract]. You get your 1through7 with that. It's only a hack, though. You are displaying the [Rank by Contract] filter, and if your data happens to have all the values from 1through10, and the user only selects 2,4,6 and 8, you can't get your counting calc to "see" those check box selections. If you are only looking to weed out nulls, then what I did will serve your needs. But if you actually want to let the user have a multiselect filter, I'm not sure you're going to be able to get that criteria to the counting calc.

2. Re: Counting Rows With Table Calcs
Joe Oppelt Apr 30, 2018 9:57 AM (in response to Joe Oppelt)So yes, It's not a "super simple" question. Table calcs are a powerful tool, and the table calc filter is designed to work the way it does (specifically leaving the underlying table intact) so that other table calcs (such as doing a percent to total) still have the whole table to calculate the "oftotal" portion of the calculation.

3. Re: Counting Rows With Table Calcs
Jonathan Hodge Apr 30, 2018 11:48 AM (in response to Joe Oppelt)Thanks so much for the answer Joe. I learned a lot from this
So with my full data set I have upwards to 10 ranks. So if they filtered for just rank 2's this calculation won't be sufficient I presume.
Interesting way for it to work in Tableau. I am surprised that such an "easy ask" turned into such the ordeal! lol
If only there was a way to set even table calc's to context filters, or if I could just drag out the summary of a worksheet onto the dashboard with the row count on it.
With this idea would it be possible to write out an elaborate case statement? So that "running_sum (If table calc = rank 1 then 1, if rank 2 then 1)". But I don't know if that would ever sum up a selection of say, rank 2,5,7.

4. Re: Counting Rows With Table Calcs
Joe Oppelt Apr 30, 2018 12:15 PM (in response to Jonathan Hodge)Jonathan Drummey  Is there a way to detect individual values selected in a table calc filter?

5. Re: Counting Rows With Table Calcs
Don Wise Apr 30, 2018 12:53 PM (in response to Jonathan Hodge)Hi Jonathan,
Please see attached packaged workbook. If you're only attempting to count rows, I was able to place the INDEX function at the beginning of the ROWS shelf, but I also needed to modify your Ranking calculation. Please see below screenshots, which automatically changed from your previous result to an ordered result. Hopefully this helps with your issue?
Thanks, Don
Original Rank Calculation (please notice the order of the Index field in the table):
Revised Rank Calculation (please notice the ordered/numbered rows) in the table:

6. Re: Counting Rows With Table Calcs
Joe Oppelt Apr 30, 2018 12:44 PM (in response to Don Wise)Don  What you did there is eliminate the RANK_PERCENTILE step, which eliminated the Table Calc behavior from the filter. And then INDEX can just do its thing on the remaining rows on the sheet, because that's all that's in the underlying table as well. I don't know if Jonathan has to have the RANK_PERCENTILE in the equation for other reasons or if the aggregate calc in your modification suffices in his actual data set.
Here's hoping for the latter!

7. Re: Counting Rows With Table Calcs
Pablo de Dios Garcia Apr 30, 2018 12:49 PM (in response to Jonathan Hodge) 
8. Re: Counting Rows With Table Calcs
Jonathan Drummey Apr 30, 2018 1:06 PM (in response to Joe Oppelt)Hi Joe,
Inside a calculation we can't detect a table calculation filter because table calculation filters are applied after other table calculations are complete. However there are other methods of working with Tableau's order of operations...
I took a quick look and found a couple of issues that need to be addressed before figuring out a solution:
1) The compute using of the Rank by Contract Sales is set to Table (Across) which means the inner Decile Calc Total Net Sales also has a compute using of Table (Across) so it's just computing on the Contract Type and partitioning on all the dimensions on Rows. Therefore the Decile calc is returning 1 for every row that has a nonNull value for Net Sales, here's a screenshot:
I'm guessing that the Decile calc is supposed to have a compute using on the dimensions on Rows similar to how the Index and Calculation1 have compute usings of Table (Down).
FYI the workbook that Don posted is similarly putting all the nonNull values of Net Sales as Rank 1 in a different way...it's doing a SUM(Net Sales) which is either returning a value >1 (and therefore is Rank 1 and passing the filter) or Null (which is the Null rank).
2) The Decile Calc Total Net Sales has the formula RANK_PERCENTILE(WINDOW_AVG(sum([Net Sales]), 0 , 10)). With the Compute Using this has one, perhaps three additional problems:
a) It's averaging Net Sales across the Contract and NonContract dimension.
b) With the offset of 0,10 that means that if there are multiple values of SUM([Net Sales]) for each row then the WINDOW_AVG() will return different values which would cause the RANK_PERCENTILE() to return different values *for the same row*.
c) The offset of 010 also means that only up to 11 marks will be included in the calculation, is that accurate?
Can you describe what this calculation is supposed to be returning in plain language? Then we can translate that into what Tableau needs and figure out whether you can get the desired results or not. I've got at least two ideas in mind that could theoretically work but I need my questions answered first.
Jonathan

9. Re: Counting Rows With Table Calcs
Jonathan Hodge May 3, 2018 8:26 AM (in response to Jonathan Drummey)I am *so glad* you clarified something that was bugging me forever!!! And it was the calculation goals lol.
A coworker tossed this to me to edit.
RANK_PERCENTILE(WINDOW_AVG(sum([Net Sales]), 0 , 10))
This is for users to see essentially the "top 90 percentile" for example but in limits of 110. I'm not the best at Window calculations so all I did was google and research to understand and to my knowledge the 0,10 was just offsets of what it would use to calculate AVG.
With that said the new question would be how to calculate a proper decile calculation in tableau haha. The "Rank 1, Rank 2, Rank 3...Rank 10" was to let the user choose the top, or lowest, or middle customers.
Sorry to twist the discussion off topic but the calculation is certainly an issue on it's own then still counting the results returned would be fantastic.

10. Re: Counting Rows With Table Calcs
Jonathan Drummey Apr 30, 2018 2:28 PM (in response to Jonathan Hodge)Table calculations are one of the most complex aspects of Tableau to understand. I can safely say that I know Tableau's table calculations better than just about anyone and I'm still learning new things about them. And I've found that there's no "one" way to teach table calculations, here are some things to help:
We can think of all the marks in the view as a "table" with "subtables" created by the dimensions used for partitioning. The subtables are "partitions" in Tableau terminology. For each table calculation the marks in each partition are then ordered (the default is based on the dimension order in the view) and that creates a sort of "number line" of the marks. The offsets used in WINDOW_ calculations are then *relative* positions on the number line.
For example if I have product A with sales on 1 Jan, 3 Jan, and 5 Jan and product B with sales on 1 Jan, 7 Jan, and 3 Feb and a table calculation with compute using on the Date then there will be two partitions (for each product) with two number lines. From the 2nd mark an offset of 1 will point to the 3rd mark in each partition, so 5 Jan for product A and 3 Feb for product B.
Therefore the offsets are all about what's included in the computation. And since we can nest table calculations and every single one can have different compute using, partitioning, sorting, and offsets we need to be careful about how we work with them.
So to fully validate table calculations we need to at the very least:
a) know the difference between between dimensions and measures.
b) understand what dimensions are used for compute using and what are used for partitioning.
c) understand what values are in each partition
d) know the sort order that the table calculation is using
e) when using offsets understand what marks are being included & excluded from the particular calculation. For example with an offset of 0,3 in a partition of 5 marks the computation for the first mark will return a value using marks 14, the 2nd mark return a value using marks 25, then 35, 45, and finally 5. So depending on the mark the calculation will be using anywhere between 1 to 4 other marks in computing the results.
f) understand what the table calculation function is actually doing
g) when table calculations are nested inside other calculations then repeat ae for each table calculation
There's more, but that's a start.
Can you explain what '"top 90 percentile" for example but in limits of 110' means? Do you mean:
1) find the 90th percentile of customers based on Net Sales
2) then keep only the top 10 of those
Or are you just looking to be able to filter on the 10 deciles, or something else?
Jonathan

11. Re: Counting Rows With Table Calcs
Don Wise Apr 30, 2018 2:38 PM (in response to Jonathan Hodge)Jonathan(s):
I redid the Delcile Calculation and then modified slightly the Rank by Contract Sales, bringing it back in to get to the view and the ability filter by Percentile Ranking from 110, but of course in doing so, brings back the Table calc., and I lose the Row Count that Jonathan wanted as well.
Please see below and newly attached sheet labeled REVISION. Hopefully Jonathan #2 can get the row count back....

12. Re: Counting Rows With Table Calcs
Jonathan Hodge May 1, 2018 1:10 PM (in response to Jonathan Drummey)Thanks so much for that information Jonathan. It is a wealth of great stuff.
The goal of this was for a marketing team to filter by decile ranks. "What customers are in our rank 1 decile, we can target them with X. On the other hand we can target the middle deciles 4,5,6 customers with Y."
The record count was supposed to be a quick showing of "Oh hey I have X customers in my rank 1 decile". Or whatever ranks were selected.

13. Re: Counting Rows With Table Calcs
Jonathan Hodge May 1, 2018 1:13 PM (in response to Don Wise)Wow, thanks for the help with that calculation Don.
Now that I see it, it's so simple. Gonna have to let my coworker know that the window_avg was never needed!

14. Re: Counting Rows With Table Calcs
Jonathan Drummey May 4, 2018 6:58 AM (in response to Jonathan Hodge)1 of 1 people found this helpfulThe change to using a nested table calculation that @Don set up works, however it has a few drawbacks:
1) Counting the customers in each decile will require another custom calc.
2) Indexing the customers in each decile will also require another custom calc.
3) If you want a view that just has the count of customers at each decile then that will get complicated to set up (because you'd be wanting to effectively partition the count on the table calculationcreated decile and partitioning on table calculations is not wellsupported by Tableau).
4) Indexing or counting all the customers after the table calculation filter is not possible, and per your original request you'd wanted that.
Here's an alternative, I built this using the Superstore sample data because it has a lot more customers. We can easily count customers using SIZE() and rank using the builtin quick table calculations and filter without affecting the results (satisfying #1, 2, and 4 above):
Also this method enables views to be built on just the Deciles with counts & totals using regular aggregates (satisfying #3 above).
The key is that the Deciles are computed as a dimension using a set of LOD expressions:
IF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .9)} THEN
90
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .8)} THEN
80
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .7)} THEN
70
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .6)} THEN
60
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .5)} THEN
50
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .4)} THEN
40
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .3)} THEN
30
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .2)} THEN
20
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .1)} THEN
10
ELSEIF [Net Sales] >= {FIXED : PERCENTILE([Net Sales], .0)} THEN
0
ELSE //technically not needed, left here to explicitly include Null as a value
Null
END
This is a way to simulate the effects of the RANK_PERCENTILE() inside LOD expressions since LOD expressions don't support comparison (ranking/indexing) operations. Then a custom number format is used on the decile so we don't need another calculation for display.
With the decile computed as a dimension then we can have table calculations operate on that to do things like count customers in the decile using SIZE() and do ranking with quick table calculations on Net Sales, then set up a LOOKUP(MIN([Decile]),0) calc to be a table calculation filter which is applied *after* other table calculations are computed. And since it's a dimension it can be used on its own to slice/partition data, as in the Superstore Bars view above:
Here's a view showing this implemented in the original data source:
Note that I put the Contract Type dimension onto Detail and then used ATTR(Contract Type) on Columns to a) prevent unwanted data densification from the Rank in Decile calc while b) preserving the layout.
v10.5 workbook is attached, let me know if you have any questions!