6 Replies Latest reply on Jan 17, 2013 5:56 AM by jonathan.chan

    Aggregating twice - is this possible?

    jonathan.chan

      Hi,

       

      I'm having problems aggregating data twice. The easiest way to explain what I'm trying to achieve is by example below:

       

      Sample raw data:

       

      IDValue
      1a
      1b
      2a
      2b
      2c
      2d
      3b
      4c

       

      Obviously, Tableau will let me achieve:

       

      IDCOUNT(Value)
      12
      24
      31
      41

       

      What I'd really like to display is a bar chart showing how many times each COUNT(Value) appears i.e.:

       

      COUNT(Value)COUNT(COUNT(Value))
      12
      21
      41

       

      I know I get can this from SQL, but I have filters working with charts created from the original data source, and I'd like this bar chart to be affected by those filters also.

       

      Many thanks in advance

        • 1. Re: Aggregating twice - is this possible?
          Catherine Rivier

          Hi,

          So I will say I am probably most of the way there on your answer - so hopefully others with more ideas will chime in!  But I can do specifically what you ask for, which is a bar chart with the counts of unique values.  See the attached.

           

          I expanded your dataset slightly in the attached, see tab 'Data-Base' - just added a few more rows.  Then I created the simple calculated field UniqueValues:  countd([Value])

          which gives you tab 'Data-Counts' - which is just the second chart you created above.

           

          Then in tab 'Simple View (No Labels)' I created a bar chart that counts what you want.  For columns it's the UniqueValues (as discrete, but will also work as continuous); Rows its new calculated field UniqueIDs: countd([ID]).  Then in the Level of Detail shelf I brought in ID, which allows it to be split up.  Make sure Stack Marks is set to On, and it should work.

           

          Finally, for the total count, I used a little trick I know:   You can look like you're labeling the top of a bar with an invisible reference line.  If you right-click on the Y-axis and choose Edit Reference Line, you can see that this is the label you see.  I have it set to the Sum of UniqueIDs, the label is the Value, and the Line is set to None.

           

          Does this work for you?  I look forward to seeing others' ideas too!

          • 2. Re: Aggregating twice - is this possible?
            Alex Kerin

            I think I answered something similar here: http://community.tableau.com/thread/116263

             

            It took me forever to find...

            • 3. Re: Aggregating twice - is this possible?
              Alex Kerin

              Here's your data in this format with an optimized formula. Essentially it's the same as Catherine's but with a window_sum to pull it together (this effectively does the same as your ref line Catherine).

               

              I still had to fiddle with right clicking the countd of Values to deselect ignore for table calculations and then refiddle with the partitioning (as the countd of Value does not appear in the advanced dialog box). Jonathan Drummey do you have any insight as to when "Ignore in Table Calcs" appears, and why the dimension does then not appear in the advanced dialog?

              • 4. Re: Aggregating twice - is this possible?
                Jonathan Drummey

                The one thing I'd add to Alex's worksheet is to Ctrl+drag a copy of the final count of countd table calc onto the Filters Shelf, and filter for non-Null values. This has two effects - it gets rid of the Null values indicator in the lower left, and in my experience seems to speed up rendering of charts that have 10s of thousands of marks.

                 

                @Alex - "Ignore in Table Calculations" is an option for discrete regular aggregates. So it's not available for table calcs or continuous regular aggregates. And we don't see it for non-aggregated fields (continuous or discrete) because those are dimensions, and dimensions are always paid attention to in table calcs.

                 

                As to why we don't see those fields in the Advanced Compute Using dialog, that's because they are not available for addressing, only partitioning. So rather than put something on the left-hand side of the dialog - the partitioning section - that can't be moved, Tableau has left that out. Personally, I'd like it to be there but have an indicator that it's not available for addressing. (Actually, what I really want is a whole new interface for setting up table calcs, here was an early attempt at an Idea on the subject: http://community.tableau.com/ideas/1189).

                 

                My guess as to *why* a discrete measure that has "Ignore in Table Calculations" unchecked is not available for partitioning is that since the results of measures depend on the other dimensions and discrete measures in the view, it makes a sort of sense that Tableau wouldn't use them for addressing just yet because that would add a lot of computational complexity to the code.

                 

                Does that make sense?

                 

                Jonathan

                • 5. Re: Aggregating twice - is this possible?
                  Alex Kerin

                  It does, and yet another little intricacy. Thanks Jonathan.

                  • 6. Re: Aggregating twice - is this possible?
                    jonathan.chan

                    Thanks, all for your time and responses. I'll take a look and have a go on my own data then feed back / mark answers when that's done. Much appreciated.