14 Replies Latest reply on Apr 21, 2016 1:34 PM by michael pinheiro

    How to Count Number of True/False/Null Values IN a Table

    michael pinheiro

      New to the forum and new to Tableau.

       

      I have created a worksheet that compares descriptions across two columns from different data sources and returns a value of "True", "False" or "Null" using the aggregate function:

       

      ATTR([ARTICLES (PELICAN_RPTG)].[ProFiles Description]) = ATTR([LiMa Description]).

       

      I would like to include a count in the worksheet of the total numbers of "True", "False" and "Null" values that occur across all rows.

       

      Any suggestions on the most effective way to accomplish this would be much appreciated.

       

      I have attached the workbook for reference.

       

      Message was edited by: michael pinheiro Re-attached workbook with data sources extracted.

        • 1. Re: How to Count Number of True/False/Null Values IN a Table
          Joe Oppelt

          I can't get that workbook to open.

           

          Extract your data sources (if you did not do that) before saving the packaged workbook.

          • 2. Re: How to Count Number of True/False/Null Values IN a Table
            michael pinheiro

            Thanks Joe.  I re-posted with the workbook with both data sources extracted.

            • 3. Re: How to Count Number of True/False/Null Values IN a Table
              Joe Oppelt

              I addressed this two different ways.

               

              On your original sheet I changed what is getting displayed to three different measures.  Then (just for the TRUE measure -- you can do the rest) I did a window count and popped that into the title field.  (You can do what you want with it.)  In this one I just set the value if the individual measures to 1, but you can do whatever you want to see there.

               

              On the second sheet, I went back to the way you originally had it displayed.  I made a second window_count calc that essentially incorporates what the individual measures on the first sheet did, right in the table calc.

               

              I like the second method better, now that I see it.

              1 of 1 people found this helpful
              • 4. Re: How to Count Number of True/False/Null Values IN a Table
                michael pinheiro

                Thanks Joe.

                 

                I haven't seen the results of a calculated field displayed in the title field like this.

                 

                Can you explain how this is done?

                • 5. Re: How to Count Number of True/False/Null Values IN a Table
                  Joe Oppelt

                  See Sheet 2 in the attached.

                   

                  I added a calc for FALSE too,

                   

                  Drag the table calc to the data shelf.  Then it's available to use anywhere in the sheet.

                  Right click on the title.  (If the title isn't being displayed yet, click WORKSHEET -> "Show Title" )

                  Click "Edit Title".

                   

                  You can to a million things in the title, really.  Here, I put both calcs, and I formatted one with color and font.  You can mess with sizes, colors.  There are tricks to display stuff conditionally (subject matter for another day.)

                   

                  By default, Tableau puts the sheet name in the title.  You can delete that.

                  • 6. Re: How to Count Number of True/False/Null Values IN a Table
                    michael pinheiro

                    Thanks Joe.

                     

                    Are you using the Insert dropdown with the Edit Title window to add the Total True / Total False count to the Title?  When I do this the title bar displays '0 to 1'.

                     

                    Maybe I am missing a step?

                    • 7. Re: How to Count Number of True/False/Null Values IN a Table
                      Joe Oppelt

                      In the data shelf, right click on the table calc field and select "Edit Table Calc".  Tableau picks what it thinks is the best way to compute the table calc.  Sometimes it picks TABLE(across).  Sometimes TABLE(down).  If Tableau hasn't picked TABLE(down) in this case, change it to that, since we're adding down the table.  (You're getting '0 to 1' because for each "across" operation the window sum is either a 0 or a 1 and nothing else.  So that's why I think Tableau has defaulted to TABLE(across) here.)

                       

                      Next, after you do that, you may need to go back into the title and re-insert the table calc.  Sometimes when you change the conditions of a table calc, Tableau recognizes previous uses of it (such as in Titles and in Labels) and doesn't pick up the new use.  When this happens you'll see in the Title editor something like <[table calc name] (3)>

                       

                      You actually can just delete that extra stuff and the space before it.  You'll see what I mean if that happens to you.

                      • 8. Re: How to Count Number of True/False/Null Values IN a Table
                        michael pinheiro

                        I think I figured it out.  I needed to set the Compute Using to Table Down for both the Total True and Total False calculated fields.

                         

                        Working now.

                         

                        Am I able to add these calculated fields to a simple bar chart?  I would like to show a simply diagram of how Total True / False across the entire data set.

                        • 9. Re: How to Count Number of True/False/Null Values IN a Table
                          Joe Oppelt

                          Sure.  Describe what you are looking to do, and I'll hack up that in another sheet.  Just 3 bars of the three values?

                          • 10. Re: How to Count Number of True/False/Null Values IN a Table
                            michael pinheiro

                            Yes. I would like to show one bar for each value to indicate total Null, total True and Total False.  I would also like to show the percent of total for each value. (e.g., x% Null, x% True, x% False).

                            • 11. Re: How to Count Number of True/False/Null Values IN a Table
                              Joe Oppelt

                              Half way there.


                              See attached.

                               

                              Step by step, here is what I did:

                               

                              Copy Sheet 2 to Sheet 3.  Add a "Null" table calc.

                              Move all the row pills to the data shelf.  (You need the dimensions to break the T/F apart by row.)

                              Change the table calcs from Table(down) to advanced, and move all the dimensions into the addressing box so that it still calcs along all the dimensions as when they were on the ROWs shelf in Sheet 2.)

                              Fix up the title so the numbers work again there.

                               

                              You'll see that all the marks for all the dimensions are still stacked up on the sheet.  But the table calcs have the exact same number in each mark (for a given color).

                               

                              Copy Sheet 3 to Sheet 4.

                               

                              Change the viz to use the MeasureNames/MeasureValues technique to display multiple measures.

                              Index=1 on the filter so that we see only one instance of each color.  (Take that off the filter shelf and scroll up and down the bars, and you'll see what I mean.)

                              CHange the "Edit Table Calc" setting on the filter to Advanced so it is evaluated just like the other table calcs.

                               

                              Now you have your three bars.

                               

                              What is missing is this:  I did not do the % of total.  I could make 3 calcs that sum up the three table calc values, and in each respective calc divide [Total False]/([total False]+[Total True]+[Total null])

                               

                              But the three measures are not separated by a single dimension, so I would have to put all three calcs into the labels shelf, and those three values would show up on all three bars.  (See Sheet 5.  I did two of them.)

                               

                              The root of this problem is that you have a data blend, and you are calc-ing your T/F based on what's in the blend.  That forces all the calculations into the measures shelf, and you can't move them up to dimensions.

                               

                              I see that you have a 1:1 correspondence in these data sources.  You could reshape your data to have it all in one data source, and then your T/F calc could actually become a dimension (and the need for three sets of calcs to handle the three conditions could be collapsed into one set of calcs.)

                               

                              It's worth considering.

                              • 12. Re: How to Count Number of True/False/Null Values IN a Table
                                michael pinheiro

                                Thanks so much for the previous response.  I appreciate the advice and the effort put into clearly defining the steps.

                                 

                                I consolidated and simplified the data source in the attached version.  The prior Null values are now treated as False values so there are only two conditions to evaluate: True or False.

                                 

                                I was able to create %True and %False calculated fields that are displaying in the title bar of first Article Comparison tab (tab one).  I was also able to create a two bar graph on Sheet 4 but am not sure how to add the total count and % true/false labels to the bars.

                                 

                                If you can provide direction on the label visibility on the bars this should take care of it.

                                • 13. Re: How to Count Number of True/False/Null Values IN a Table
                                  Joe Oppelt

                                  Oh wow.  Moving this into one data source made this infinitely easier!

                                   

                                  I made a copy of your Description Match calc to remove the ATTR() functions.  To human logic they are equivalent, but to Tableau's logic they are worlds different.  The ATTR() function is treated boy Tableau as an aggregate function.  That forces the result of the calc to be a measure, and only a measure.  (Yes, there are times when you MUST put the ATTR() in there.  When you are comparing a row-level value to an aggregate, you must promote the row-level value to an aggregate, and ATTR() is a way to do that.  And when you are looking at data from a secondary source -- even when you only have a 1:1 correspondence -- Tableau always considers the secondary data as an aggregate.)

                                   

                                  Now that we're in a single data source, we can get rid of ATTR() for this calc.  And when we do, it can become a dimension, and that's what we have here now.

                                   

                                  So look at sheet 5.  Here, each row is either a true, or a false.  That's it.  For the purposes of this exercise, simply using sum(number of records) is all we need to do.  In your expanded workbook it may be a count of something.  Because it is split up by an actual dimension field, Tableau treats each value within that dimension (in this case just T or F) the same way, and one pill takes care of both bars.  And in this case I could use the built-in quick table calc: percent of total.  Again, one pill, both bars.  In your expanded example you may have to build the table calc manually (something like we did in my old Sheet 4.)  But again, one pill will take care of both bars.

                                  1 of 1 people found this helpful
                                  • 14. Re: How to Count Number of True/False/Null Values IN a Table
                                    michael pinheiro

                                    Well that hole process went from two days to two minutes!

                                     

                                    Just goes to show the value of knowing a little SQL.

                                     

                                    Thank you again for your time and knowledge.