11 Replies Latest reply on Apr 27, 2018 8:27 AM by Joe Oppelt

    How do I show the total count of ata values

    Tiago-Mateus Korynek

      Hi all,

       

      I have a workbook where I have 2 dimensions that I was to use to show/find out which Atas occur the most as each STN (station).

      I have setup the filters for my data and assembled the crosstab with Ata2Digit on the column and STN on the row. To get something like in the image below:

      This looks like how I want it to appear, but when I add CNT(Ata2Digit) to the visualisation the values which appear do not match with what I am expect comparing it to the raw data and gives me the below:

      ANT for starters should only have a maximum of 12 records in total and each record should fall into a different Ata2Digit...

       

      Any help would be greatly appreciated and I am using Tableau 10.4.1.

        • 1. Re: How do I show the total count of ata values
          Tiago-Mateus Korynek

          Apologies I forgot to add the filter WeekFilter to refine the linked records to just those from TblTcDel by making sure the date fell between week start and week end.

           

          I now have it calculating correctly, but I now need to work out how to present the name of the Ata2Digit which occurs the most frequently in the table for each STN.

           

          I would eventually like it so that the table below becomes the table beneath it:

          I don't know how to deal with instances for example with ANT where there is no "Top Ata2Digit" meaning multiple could be considered. Is there a way to get it so that it shows it in a format like 23/25/26/27?

          • 2. Re: How do I show the total count of ata values
            Joe Oppelt

            You wan to use COUNTD, not COUNT.


            See attached.

             

            COUNT gives a count of records -- including duplicate values.  COUNTD gives distinct counts.

            • 3. Re: How do I show the total count of ata values
              Joe Oppelt

              Or maybe you needed to filter something. 

              • 4. Re: How do I show the total count of ata values
                Joe Oppelt

                So re-upload your workbook with the added filter so I can help you from there.

                • 5. Re: How do I show the total count of ata values
                  Tiago-Mateus Korynek

                  Apologies - now attached.

                   

                  That first reply I did, I replied using the advanced editor so that I could reupload it, but I forgot to do it. Doh!

                  • 6. Re: How do I show the total count of ata values
                    Joe Oppelt

                    Here is a step-by step of what I did:

                     

                    Sheet 1 is your original sheet.

                     

                    Sheet 2 is a copy, with a table calc added to show the biggest value for each STN.  It finds the biggest in each STN because, by default, tableau evaluates this tables calc by looking at the table-across.  (Along each row.  And each row is a STN.)  Take a look at the [Biggest Digit] calc to see the syntax.

                     

                    Sheet 3:  I took [Biggest Digit] off TEXT, and added a new calc that sets a value of 1 where the CNT is equal to the biggest digit.  Now we get a 1 wherever the value is the biggest digit.

                     

                    Sheet 4.  Here I build a string to keep adding to a growing list along the row.  It uses a cool function called PREVIOUS_VALUE.  If I hit the first incident of [Does this have the biggest digit] I just shove in the value.  But if I hit a subsequent one, I tack on "/" and then the value.  And as we go down the row, the string grows.  Don't worry about overflow on the display.  This is just an interim step anyway.

                     

                    Sheet 5.  Here I took everything else off the sheet and just display a new calc that grabs the last value of the growing list.  Look at [Ending List].

                     

                    Sheet 6.  This is where it gets tricky.  We only want to show one of these for each STN.  We need both STN and ATA2DIGIT on the sheet because Tableau needs both dimensions to walk the table and do the work.  I dragged Ata2 to the detail shelf.  But this changes what TABLE(across) means because the shape of the table was previously defined by the layout of the sheet.  Now I have to specify to Tableau how that table calc needs to operate.  You see a triangle in the pill for [Ending List] on the TEXT shelf.  Right click that triangle and select "Edit Table Calc".  You get a box that looks like this:

                     

                     

                    I selected "Specific Dimension" and checked both dimensions.  I reordered them so that STN is at the top.  (You just drag them in the list to reorder.)  And I told the calc to restart for each STN.


                    Further, there are multiple table calcs that go into the evaluation of [Final list].  You can see them all in the pulldown (which I circled in red.)  I had to make the same setting for each calc in that list.  Once I did that, you can see the results on sheet 6.  We get duplicate values of this calc because a table calc gets set for each dimensional combination of the dimensions on the sheet.  Go to Sheet 7.

                     

                    Here I did one more table calc.  I had to set it the same as what we did in Sheet 6.  Look at the filter I added:  [Just show one value].  The LAST() function identifies the last element in a string of values in a table.  I'm showing the last one down the string of ATAs for each STN.

                     

                    There is the sheet you're looking for.


                    See attached.

                    1 of 1 people found this helpful
                    • 7. Re: How do I show the total count of ata values
                      Tiago-Mateus Korynek

                      That answer was flipping amazing Joe and really appreciated. The way you broke it down and explained everything means I could even use it as an example exercise when trying to teach what little I know to my colleagues that might want to dabble in Tableau and blank out certain steps and ask so "How should we proceed from this point?".

                       

                      There were a couple of formulae that I didn't even consider to use, but following your steps it clicked straight away.

                       

                      Props to you and the speedy response.

                      • 8. Re: How do I show the total count of ata values
                        Tiago-Mateus Korynek

                        As this is a text table, how would you go about putting a header on the data of ATA2Digit in the right most column? Would you go for the "layer a floating text object above it" technique?

                        • 9. Re: How do I show the total count of ata values
                          Joe Oppelt

                          In the attached, in sheet (7) I dragged [Ending List] from TEXT to ROWS.  This, however, leaves an empty mark to place whatever  measure(s) you might want to display.  (Tableau makes the assumption you will do that.)

                           

                          In sheet (8) if set the color to be white.  The mark is still there, but it's the same color as the sheet, so it looks invisible.

                           

                          In sheet (9) I just dragged the size of the mark column so that it's the minimum I could make it.

                           

                          So now if you renamed [Ending List] to be whatever you want that header to be, you'll have what you need.

                          • 10. Re: How do I show the total count of ata values
                            Tiago-Mateus Korynek

                            This was the alternative technique I was also thinking about. I just didn't know how to describe it. I usually switch it to Polygon instead of square, but I guess it doesn't really matter when it gets hidden.

                             

                            Is there a specific reason why this is a bit awkward in Tableau? Is there a purpose for it being this way or is it just ridiculously complicated to try and code it and they are currently working on it?

                             

                            Either way this meets my needs, but I think it might give me a random line in the header box between the column with the ATA in and the "blank" column when printed oh well at least it doesn't show on the workbook and dashboard.....

                            • 11. Re: How do I show the total count of ata values
                              Joe Oppelt

                              Tiago-Mateus Korynek wrote:

                               

                               

                               

                              Is there a specific reason why this is a bit awkward in Tableau? Is there a purpose for it being this way or is it just ridiculously complicated to try and code it and they are currently working on it?

                               

                               

                              Tableau expects some sort of data to analyze.  That's the underlying purpose of Tableau:  Data analysis.

                               

                              A lot of people still use Tableau for crosstab reporting, as you are doing here, but Tableau is still itching to do powerful things on the measure data that corresponds to the dimensional values you are showing in that chart.