1 2 Previous Next 16 Replies Latest reply on May 7, 2018 7:52 AM by Jonathan Hodge

    Counting Rows With Table Calcs

    Jonathan Hodge

      Howdy All!

       

      I've been busting my head against what should be (I'd think) a super simple issue. I'm sure it is right in front of me and I'm glancing over it.

       

      I have some data in table format and simply want to show the user how many rows returned.

       

      I have tried Index, Fixed calcs, Running totals, WINDOW_SUMS, Max, Size, and combo's of all above. My number just never matches what Tableau says in the bottom left of the worksheet (X rows by X columns) and I wish *so drastically* there was a way to just drag that out onto a dashboard!

       

      I am utilizing a table calc in the dashboard filters which limits some data off. This filter is what seems to ruin other table calcs when counting rows.

      I'm sure there is a solution though. In the above picture I am looking to achieve the number 7

       

      Thanks for any assistance!

        • 1. Re: Counting Rows With Table Calcs
          Joe Oppelt

          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 1-through-7 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 1-through-10, 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 multi-select 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

            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 "of-total" portion of the calculation.

            • 3. Re: Counting Rows With Table Calcs
              Jonathan Hodge

              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

                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

                  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):

                  Screen Shot 2018-04-30 at 10.17.25 AM.png

                   

                  Revised Rank Calculation (please notice the ordered/numbered rows) in the table:

                  Screen Shot 2018-04-30 at 10.17.32 AM.png

                  • 6. Re: Counting Rows With Table Calcs
                    Joe Oppelt

                    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

                      Hello Jonathan,

                      remove the field Index, change it for discrete field. place again using the table option (below)

                       

                      1.

                      2.

                      • 8. Re: Counting Rows With Table Calcs
                        Jonathan Drummey

                        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 non-Null value for Net Sales, here's a screenshot:

                         

                        Screen Shot 2018-04-30 at 3.52.57 PM.png

                         

                        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 non-Null 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 Non-Contract 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 0-10 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

                          I am *so glad* you clarified something that was bugging me forever!!! And it was the calculation goals lol.

                           

                          A co-worker 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 1-10. 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

                            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 1-4, the 2nd mark return a value using marks 2-5, then 3-5, 4-5, 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 a-e 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 1-10' 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

                              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 1-10, 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....

                               

                              Delcile Calc Updated.png

                              Ranking Updated bu t loses Row Count (Index Function).png

                              • 12. Re: Counting Rows With Table Calcs
                                Jonathan Hodge

                                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

                                  Wow, thanks for the help with that calculation Don.

                                  Now that I see it, it's so simple. Gonna have to let my co-worker know that the window_avg was never needed!

                                  • 14. Re: Counting Rows With Table Calcs
                                    Jonathan Drummey

                                    The 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 calculation-created decile and partitioning on table calculations is not well-supported 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 built-in quick table calculations and filter without affecting the results (satisfying #1, 2, and 4 above):

                                    Screen Shot 2018-05-01 at 6.00.36 PM.png

                                     

                                    Also this method enables views to be built on just the Deciles with counts & totals using regular aggregates (satisfying #3 above).

                                     

                                    Screen Shot 2018-05-01 at 6.04.44 PM.png

                                     

                                    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:

                                     

                                    Screen Shot 2018-05-04 at 9.53.38 AM.png

                                     

                                    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!

                                    1 of 1 people found this helpful
                                    1 2 Previous Next