3 Replies Latest reply on Oct 31, 2012 1:44 PM by Richard Leeke

    bins using group totals

    Craig Mullins

      data :

       

      dimensions/amounts 2012 charities:

      • red cross-one  $50
      • cancer society $100
      • aspca $200
      • church $75
      • red cross-two $2000

       

      i need to bin the data above into $100 donation group bins (can do) - BUT, i need "red cross-one" and "red cross-two" to be treated as one charity and the total of that group to be used as one charity for bin placement. so, bin 4 charities not 5.

       

      how can i have the bin use the "red cross" group total for binning? 

        • 1. Re: bins using group totals
          Richard Leeke

          One option is to create the bins with a calculated field like this:

           

          INT(SUM([amount])/100)*100

           

          I pasted your data into the attached example.

          • 2. Re: bins using group totals
            Craig Mullins

            this looks like it works - Thanks!

            it is so simple yet i cannot figure out how and why it works.

            could you briefly walk me thru your steps.

            • 3. Re: bins using group totals
              Richard Leeke

              So all I have done is to create a normal Tableau group, to group the two red-cross values together. Then,instead of using Tableau's bins (which work out which bin each individual row in the underlying datasource falls into) I have defined a calculated field which works out the total for each group member before calculating which bin it falls into.

               

              So the SUM([amount]) gives you the total for each group member (as long as you have the group on the view somewhere), and then the expression:

               

              INT(X/100)*100

               

              is working out the bins (divide by 100, take the integer part and then multiply by 100 again).

               

              If you actually have more dimensions in play (such as a time period, for example) you will need to think carefully about what level of aggregation the SUM() is giving you. You might even need table calculations to get the degree of control you need if that is the case.