1 2 Previous Next 22 Replies Latest reply on Oct 15, 2013 7:02 AM by Jonathan Drummey

    Count rows

    Amy Song

      I have a view that shows hours of the day that exceed a certain measure (see filters in attached workbook).

       

      So for example you see in the attachment, 10pm exceeded my filter limit on 8/9, and 9pm and 10pm on 8/10 etc... I am trying to design a view that just shows the number of distinct hours (I just want the row count per day).  So it would have day, and a number... 0, 1, 2, etc.

       

      I tried doing a table calc on hour(TimeStampHour), but got a "Measure expressions must be aggregate or depend only on GROUPBY columns" error.

       

      Can you please help?  Thanks!

        • 1. Re: Count rows
          Shawn Wallwork

          Amy I really don't understand what viz you're ultimately after, but I suspect you're looking for the SIZE() function. (See attached.)

           

          --Shawn

          • 2. Re: Count rows
            Amy Song

            Hi Shawn,

            Sorry to be unclear, but that is not what I'm looking for.

             

            Right now, the table appears such as:

             

            August 4, 2013     |     10pm

            August 5, 2013     |     10pm

            August 6, 2013     |     9pm

                                          |     10pm

            August 7, 2013     |     9pm

                                          |     10pm

             

            What I would like the table to show is:

             

            August 4, 2013     |     1

            August 5, 2013     |     1

            August 6, 2013     |     2

            August 7, 2013     |     2

             

            Which are basically the count of rows appearing in my attached workbook.  What I'd like to ultimately do is be able to plot them in a line/bar graph by day over time.

             

            Thanks!

            • 3. Re: Count rows
              Jonathan Drummey

              Hi Amy,

               

              Do the Camera_name and ZoneID need to be in the view (on Rows, Columns, Pages, or Marks Card) or not? It looks like at least the ZoneID needs to in order to segment the SUM(Density). Also, does the result of SUM(Density) need to be displayed?

               

              The reason that I ask is the table calculations change depending on the dimensions in the view.

               

              Jonathan


              • 4. Re: Count rows
                Amy Song

                Hey Jonathan,

                Yes, it would need to be by camera (which is the location of the segment of roadway) and zone (ids are lanes in that segment of roadway).  Density > 45 is the industry standard for "poor" level of service, like a traffic jam.  So what I'm looking to design is a dashboard with this component of showing how many hours in a select day a camera (location) served poor service, with poor service defined as density>45 in either lane of the location.  Hope that helps clarify.

                • 5. Re: Count rows
                  Jonathan Drummey

                  Will just one camera be selected at a time or multiple cameras?

                  • 6. Re: Count rows
                    Amy Song

                    Ultimately the view will show multiple, but one is good per sheet -- I can put them adjacent on a dashboard.

                    • 7. Re: Count rows
                      Amy Song

                      I should also add there will be no selection for the consumer, it will show a static set of cameras & info about them.

                      • 8. Re: Count rows
                        Aaron Clancy

                        Is the chart below on the right the correct values for what you're looking for with the data on the left?

                         

                        Screen Shot 2013-08-15 at 10.54.02 AM.png

                        • 9. Re: Count rows
                          Amy Song

                          Aaron, that is very helpful & close, but I just need one column; if one lane has poor service, I would want to count that hour for the whole camera.  So there would be one column and it would contain: 3, 3, 4, 3, 2, 2, 3.

                          • 10. Re: Count rows
                            Aaron Clancy

                            That actually makes things much easier lol let me know if this is good.  If so, I'll attach the wkbk

                             

                            Screen Shot 2013-08-15 at 11.06.16 AM.png

                            • 11. Re: Count rows
                              Amy Song

                              Yes, exactly!!

                               

                              Edit: Can this be able to be plotted in a line graph?

                              • 12. Re: Re: Count rows
                                Aaron Clancy

                                Line might get a little tricky because of the discrete values needed for the size calc.  Bar Chart is easy.

                                 

                                 

                                Let me know if the workbook helps.

                                 

                                Thanks

                                • 13. Re: Count rows
                                  Amy Song

                                  Thak you Aaron!  There seems to always be something new to learn with Tableau

                                  • 14. Re: Re: Count rows
                                    Jonathan Drummey

                                    Aaron and I were separately but simultaneously working on this, here's a separate version that has a line graph and pads out the data to deal with what happens when the density goes to 47 or higher (August 9 gets filtered out).

                                     

                                    I chose to use Tableau's domain padding by turning on Show Missing Values for the MDY(TimeStampHour). Because we're now padding, the SIZE() function won't work anymore because it will count any padded rows. Also, because of the needed Compute Usings we end up with padding for the HOUR(TimeStampHour), so I created a string for that to avoid padding that out as well, I was curious about how much padding would exist so there are two versions of the worksheet, one with minimal padding (usgin the string hour) and one with more padding (using the HOUR(TimeStamp Hour), the latter creates anywhere from 3x to 8x as many marks.

                                     

                                    What I did was to flag each hour that has one or more ZoneID's that have a value, then sum up the # of hours for the day. there's a calculation called Density Flag that identifies whether any ZoneID for a given time has a Density >0 (i.e. has passed the Density Filter) and returns 1 for the Hour, that has a compute using of Hour. Then the # of Hours Met calc sums those up for each day, with a Compute Using of the Hour and ZoneID. From there, building a line chart is pretty straightforward. I also put a copy of the # of Hours Met on the Filters Shelf, this hides the Null marks warning and reduces the number of marks Tableau will try to draw (or warn about).

                                     

                                    Aaron, one suggestion on your table calc setup, it's a bit more complicated than it needs to be for this particular case. The Camera, Day, Hour, and ZoneID were all part of addressing, with At the Level on Hour and Restarting Every Day. In the Advanced Dialog, everything on the left is automatically part of partitioning, everything on the right is part of addressing, and Restarting Every lets us move the partitioning into one of the addressing dimensions. So, instead of putting everything in addressing, we can put just the needed dimensions - Hour and ZoneID - into Addressing, let Tableau automatically partition on Camera & Day, and then have At the Level on Hour:

                                     

                                    2013-08-15 13_26_00-Tableau - forum3.png

                                     

                                    I find this a bit easier to read.

                                     

                                    Joanthan

                                    1 2 Previous Next