10 Replies Latest reply on May 20, 2013 11:00 AM by Jonathan Drummey

    How do I count number number of unique entries that fall within a bin?

    brian.comeau

      Hi Experts,

       

      I've attached a file (image) that I'll be referring to for this question.

       

      What I want: A stacked column chart that shows the count of 'intRestId' at within each '(DEL) Bins' along '(DEL) Updated Week Ending' where colors are defined by '(DEL) Bins'

       

      Ideally, I would want anything that has a negative Bin to be below the 0 on Y axis, but that is another problem to address later.

       

      In the attached picture, I can count (visually) the number of 'intRestId' that fall within each '(DEL) Bins', but I can't seem to be able to get these to stack together to form a column. When I try to do this, it is partitioned by each 'intRestId' rather than simply having the count.

       

      Is there something I'm missing here? I'm sure this is an easy fix and I'm just not seeing it.

       

      Thanks,

      Brian

        • 1. Re: How do I count number number of unique entries that fall within a bin?
          Jim Wahl

          If you want to show count of intResId, then you'll need to make it a measure. On the pill, right-click > Measure > Count.

           

          Stacked bar charts are simply bar charts with color applied, so if you want the count of intResId to be your bars, then you'll want to move this (continuous measure) to the Rows shelf and remove (DEL) Bins from the Rows shelf.

           

          To make the negative values go below the x-axis, check out Joe Mako's viz here that used Gantt bars.

          http://www.tableausoftware.com/public/gallery/how-teens-research

           

          Jim

          • 2. Re: How do I count number number of unique entries that fall within a bin?
            brian.comeau

            Hi Jim,

             

            Thanks for the reply. Unfortunately, if you take a look at the image I've provided, the bins are calculated at the 'intRestId' level (ideally) and then I would need the aggregation/count after that. This isn't normally the way that table calculations work, which is where I'm having the problem.

             

            If I merely put a Count (or count distinct in this case) on intRestId and use that instead, then my bins only apply to the aggregated value across all intRestId rather than for each intRestId.

             

            Any other ideas?

             

            Thanks,

            Brian

            • 3. Re: How do I count number number of unique entries that fall within a bin?
              Jim Wahl

              Part of me didn't think it was going to be that easy.

               

              However, if you want bars, then you'll need a continuous measure. Since you're using table calcs to compute the bin, perhaps you could create another calculated field that's WINDOW_COUNT([(DEL) Bins]) --- now this is a continuous measure and you still have intRestId on the detail shelf.

               

              Just guessing---if you're still stuck and no one else chimes in, perhaps you could most a subset of the data as an example. ...

               

              Jim

              • 4. Re: How do I count number number of unique entries that fall within a bin?
                Jonathan Drummey

                @Jim - I'm not sure the WINDOW_COUNT() would work because it seems like it would need to partition on the (DEL) Bins field, which is a table calc, and we can't partition on the results of table calcs. (My first thought had been to use SIZE(), but that runs into the same problem).

                 

                I'm almost certain this can be done, perhaps with a BYO partitioning solution, but I'd need some sample data & a packaged workbook. The exact route to take will depend on how the (DEL) Bins table calc is put together.

                 

                Jonathan

                • 5. Re: How do I count number number of unique entries that fall within a bin?
                  brian.comeau

                  The Bins are calculated in the standard manner we would do on table calculations:

                   

                  IF [value]>0 THEN ">0"

                  ELSEIF [value]<0 THEN "<0"

                  END

                   

                  (although this is for multiple bins)

                   

                  This is on a table calculation that is effectively a difference from First along Table (Across) at the intRestId level.

                   

                  Basically I want to be able to create something that counts the number of intRestId that are varying degrees different from First at the intRestId level.

                   

                  Hope that helps. This is from SQL Server so I don't have a packaged workbook unfortunately.

                   

                  Thanks!

                   

                  Brian

                  • 6. Re: How do I count number number of unique entries that fall within a bin?
                    Jonathan Drummey

                    > Hope that helps. This is from SQL Server so I don't have a packaged workbook unfortunately.

                     

                    I'm not familiar with SQL Server data sources, I'm pretty sure you can export a packaged workbook from any data source though. If you can't, you could generate an extract and put that in a packaged workbook, though of course there are potential size issues if your raw data is massive. I'm asking because based on your description the solution I'm thinking of will require a couple additional levels of nested table calculations and getting the Compute Usings correctly set is a lot easier to understand and duplicate when the dimension & field names are the ones you are really using.

                     

                    Jonathan

                    • 7. Re: How do I count number number of unique entries that fall within a bin?
                      brian.comeau

                      Hi Jonathan,

                       

                      I've attached a workbook based on some of the sample data.

                       

                      Same idea here that I've applied in mine. Imagine counting the number of products by year that fall within the different bins.

                       

                      I'm sure it is something to do with something relatively ingenious here that you will figure out.

                       

                      Thanks,

                      Brian

                      • 8. Re: How do I count number number of unique entries that fall within a bin?
                        Jonathan Drummey

                        Ok, here's a solution that's not ideal see the two measure values worksheets.

                         

                        First I considered doing the BYO partitioning, but sorting was an issue because the marks aren't laid out in a way that gets them in the right order to evaluate the marks without having to have a measure for every bin.

                         

                        Then I tried using blended data with a row level calc in the blend to get the first year sales, however I ran into a different sorting issue The difficult part with getting the sort right is that we want to sort the bins in the graph. However, in the blend solution the bins are determined by a regular aggregate measure, and in the measure values solution the bins are determined by a table calc. Tableau positions marks based on the sort of the pills, and we can't sort aggregates or table calcs by another measure. I tried out an idea to build a Gantt chart, but ran into the problem that we can only address on discrete measures in certain circumstances and not the pill layout I needed to complete the view.

                         

                        So I went the brute force route and built the chart using a measure names/values solution, where each bin has its own measure. Then we can control the sort of the Measure Values Shelf and get the bar segments where we want them.

                         

                        If anyone else has ideas on this, I'm all ears!

                         

                        Jonathan

                        • 9. Re: How do I count number number of unique entries that fall within a bin?
                          brian.comeau

                          Hi Jonathan,

                           

                          Thank you for taking a look at this. The solution you provided does seem to work, although I think it might be a challenge for me to maintain given the lack of flexibility. I think the best course of action in this instance is to think of another way of representing the data.

                           

                          Thanks for the help!

                           

                          -Brian

                          • 10. Re: How do I count number number of unique entries that fall within a bin?
                            Jonathan Drummey

                            The one idea in the back of my mind as I was doing this was to do more aggregation outside of Tableau. If the % difference were coming through as a pre-calculated measure, then getting the output you want would be quite straightforward.

                             

                            This is a good case for getting more functionality in terms of partitioning via table calcs and/or using a worksheet as a data source.

                             

                            Jonathan