7 Replies Latest reply on Nov 26, 2012 10:22 AM by Jonathan Drummey

    Binning based on an aggregate

    Ethan Weiss

      Hi-

      I have attached a workbook and a picture.

       

      I am trying to get a sum countD of ID's that fall within a certain bin. Currently, I am having a problem because it is taking each unique ID and counting it as 1...this is what I want, however I need them to sum to a total. For example: 15+ bin should total 3 and a label should show that.If you click on each vertical bar, it is just stacking on top of each other individual ID's

       

      Sheet 1.jpg

        • 1. Re: Binning based on an aggregate
          Jonathan Drummey

          Hi,

           

          Attached is one way to go about this. Instead of drawing 745 marks (that are stacked up into 4 bars), I set up the calculations to draw 4 marks (one per bar). Here's what I did:

           

          1. Create a calculated field called WS CountD that is

           

          IF FIRST()==0 THEN

              WINDOW_SUM([CountD],0,IIF(FIRST()==0,LAST(),0))

          END

           

          This table calculation will have its Compute Using set to ID and is set up to only return one value per partition.

           

          2. In the view, uncheck "Ignore in Table Calculations" on the blue FrqCountBins pill. This lets the WS CountD calc partition on FrqCountBins, so we get four results.

           

          3. Use WS CountD in place of CountD on Rows and set the Compute Using to ID. This gets us the four bars with a bunch of Null values (the Null warning in the corner).

          4. Ctrl+drag the WS CountD over to the Filters Shelf and set to filter for Non-Null values.

          5. Click on the Label Shelf drop-down to turn on Mark Labels.

           

          Cheers,

           

          Jonathan

          • 2. Re: Binning based on an aggregate
            Ethan Weiss

            amazing, thanks!

            • 3. Re: Binning based on an aggregate
              Ethan Weiss

              Jonathan-

              Seems like the problem I keep running into is maybe because of how I binned the records? I am trying to get now a stacked bar chart to total the 745 breaking it down by bin. It is showing each individual record instead of grouping all the ID's that belong in that bin? Thoughts

              • 4. Re: Binning based on an aggregate
                Ethan Weiss

                For example, I can't filter based on FrqCountBins?

                • 5. Re: Binning based on an aggregate
                  Jonathan Drummey

                  Hi Ethan,

                   

                  You can't filter on FrqCountBins because it is a discrete aggregate, with aggregates you can only filter on continuous values.

                   

                  I'm not sure what you're running into with a stacked bar, I'm guessing you're still using ID on the level of detail and not using a table calc to have the aggregation happen over the FrqCountBins. I've set up a stacked bar in the attached, the tricky bit was playing with the sort on ID so the bars are in the right order.

                   

                  Jonathan

                  • 6. Re: Binning based on an aggregate
                    Ethan Weiss

                    This is what I want, thanks and yes it was what you thought was the issues.

                     

                    I keep facing issues though maybe because how i set something up originally. If I right click on lets say the "4-6 visit" within the FrqCountBins, and try to exclude the entire bin (650 ID's) it only takes away 1 value and drops to 649 ID's. I want to exclude all of them in that bucket.

                     

                    Is there a better way to set up the calculation to put the ID's respectively in bins or groups?

                    • 7. Re: Binning based on an aggregate
                      Jonathan Drummey

                      The reason why it's only excluding 1 value is that you are excluding based on ID (that's what you'll see on the Filter Shelf) and not FrqCountBins, and there is only one mark being drawn for each bar that only has one ID associated with it. To exclude those, my suggestion is to have a separate calculated field that returns 1,2,3, etc. so it can be a continuous measure, and then filter on that instead of using right-clicking in the view. It's kludgy but it gets the job done.

                       

                      Jonathan