7 Replies Latest reply on Apr 5, 2012 2:22 PM by Jonathan Drummey

    Window Counts by Dimension

    Kelly Grant-Leanna

      I've attached an excel doc that contains a subset of my data source and an example of the output I need.

       

      Sheet1 is the data source.

      Sheet2 is the output desired.

       

      I want a unique listing of id, composite, and site...but I also want a count associated with each row.  This count needs to be the number of times that a specific ID shows up in my output.

       

      For id 0.  It shows up in the output a total of 10 times...thus the 10 in each of the rows with id 0 in it.

      ID 1015916 shows up three times, so a 3 in each row.

       

      The end result is to sort based on this count.

       

      I am currently running 6.1 and I cannot figure out how to accomplish this.

       

      Thank you for any assistance provided.

        • 1. Re: Window Counts by Dimension
          Jonathan Drummey

          Hi Kelly,

           

          I'm a little confused by what you want. The Sheet2 Results that you show are a just count of the # of dimensions (i.e. combinations of Composte and Site) displayed for each id. However, the wording is about how many times each id shows up, which is a different number.

           

          Since the view includes id, Composite, and Site, I think the easiest way is just to create a calculated field with the function SIZE(). Put that in your view, and set the Advanced Compute Using to include the Composite and Site fields. SIZE() returns the # of rows in the partition. By setting the addressing to the combination of Composite and Site, this sets the partition to restart every id, the function returns the # of combinations of Composte & Site.

           

          You could also do a WINDOW_COUNT of id and do similar partitioning, I set this up in the WC id field.

           

          If you want another sum, I guess I'd need a better understanding of what output you want.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Window Counts by Dimension
            Kelly Grant-Leanna

            Yes, that's what I was after.  Seems a little counter-intuitive to have to do it that way, but maybe I'm broken.  Still relatively new with Tableau so I don't understand all of it yet.

             

            select distinct d.id,c.composite,c.site,d.id_count

            from Sheet1 c

            INNER JOIN

                (select b.id, count(b.id) as id_count from

                    (select distinct a.id,a.composite, a.site from Sheet1 a) b

                group by b.id) d

            ON c.id = d.id

             

            I was thinking along these lines, where I had to group by the id to get the count I want.  Not be grouping things based on composite and site.  There's probably a better way to write the above SQL as well.

             

            Is there any way to get your workbook to sort descending based on your "size" column.  My end user wants to see them in order of most frequently showing up in the list.  I've re-attached the excel doc with the sorting I'm looking for on Sheet2.  I can't help but think there's some really easy way to do this, but I can't figure it out.  Can do it in about 2 seconds in excel.

            • 3. Re: Window Counts by Dimension
              Jonathan Drummey

              Hi Kelly,

              Setting the table calc Compute Using to Composite, Site is very similar to what you did in the innermost part of the SQL that you wrote : (select distinct a.id,a.composite, a.site from Sheet1 a) b

              group by b.id) d

              The view has the distinct combinations of ID, Composite, and Site because we put the pills on the rows shelf. In the table calc, setting the Compute Using to Composite and Site tells Tableau that we're going to perform a calculation for each intersection of those two, and then ID becomes the partitioning, which is like the Group By in that Tableau will perform the calculation for each ID. The wacky (and powerful) part with Tableau table calcs is that you can set up table calcs that make use of fields that are not in the displayed view, but are in the Level of Detail.
              In terms of sorting, if you're used to Excel, then yes, Tableau's sorting can be a little counter-intuitive at first, because Tableau sorts dimensions (based on the data sort order or by an aggregate measure), whereas Excel will attempt to sort anything.
              Looking at your attached spreadsheet, it seems like the sort order is first based on the # of counts of ID along Composite & Site, then the ID (ascending), there are manual sorts for Site and Composite (though the ordering of these two are not consistent within your spreadsheet). By default, Tableau does the ID sort, so we need to make the other sorts happen.
              What I did in Tableau was:
              1. Set up a default manual sort for Site in the Data window by right-clicking on the field and choosing Sort…
              2. Set up a default manual sort for Composite in the Data window by right-clicking on the field and choosing Sort…
              3. Created a new calculated field called Negative Size that is simply -[Size]
              4. Dragged the Negative Size to the Rows shelf. By default, Tableau uses the compute using settings for the existing Size field, and draws a bar chart.
              5. Set the green Negative Size pill on the Rows shelf to Discrete. This changes it into a blue pill and now shows text values.
              6. Drag the Negative Size pill over to the leftmost position on the Rows shelf. Your data is now sorted in descending order by Size.
              7. Click on the Negative Size pill to turn off Show Header
              8. I cleared off the other calculated fields and just left the Size pill on the Text shelf, then created a calculated field called "Count of ID" with the text "Count of ID" and put that on the Columns shelf. I turned the label off for that column. This is a workaround because Tableau won't show Measure Names/Measure Values when there is only one field in Measure Names/Measure Values.
              Let me know if this works for you!
              Jonathan
              • 4. Re: Window Counts by Dimension
                Kelly Grant-Leanna

                This works .  Thank you very much.

                • 6. Re: Window Counts by Dimension
                  akhilesh prasad

                  This was helpful. I had another question may not be completely related.

                  How does one display a column total? I used the running total column in addition to the existing column to get that number.

                  • 7. Re: Window Counts by Dimension
                    Jonathan Drummey

                    Hi Akhilesh,

                     

                    It depends on your data. The simplest way is to do Analysis->Totals->Show Column Grand Totals, though this is limited. If the totals aren't calculated the way you want, then you can do some funky calculations to override Tableau's calcs, or (more easily, but not as pretty) create a dashboard with your original view and a second view that just has the column total(s) you need.

                     

                    Jonathan