7 Replies Latest reply on Oct 11, 2017 4:22 AM by Simon Runc

    Create Bins from Aggregate value

    Puja Doona

      I'm trying to create a histogram from bins of aggregate values but not able to figure out a way to do it in tableau.

       

      The situation is as below. We have a number of items in a warehouse and each item has a cost attached to it that is associated with picking the item. So basically, there is a cost / pick metric and the idea is to create a view to visualise how many items (for a a given range of days) fall within that bin or cost / pick criteria of 1-5, 6-10, etc. So imagine, dates on x axis and count of items on y axis and a bin / colour to denote the cost / pick range.

       

      While this is pretty straight forward for a non-aggregate value, I'm dumbfounded how to do it for an aggregate.

       

      The value of the cost / pick and the number of items picked per day will vary and I want to to get a trend of the count of items in warehouse.

       

      Attached is a sample excel data file with 5 days of data and packaged workbook containing same data.

       

      Any help is appreciated.

        • 1. Re: Create Bins from Aggregate value
          Simon Runc

          hi Puja,

           

          So there are a few things to consider here...

           

          The general formula for creating a bin on an aggregate is as per the below

          INT([cost/pick]/[Bin Size])*[Bin Size]

           

          where Bin Size is a parameter, so you can change the bin size to suite.

           

          However this causes another problem...as your cost/pick is an aggregate calculation it is assessed at whatever level you have in the Viz. So if we have Date and ID in the Viz, it works fine...but remove ID and this happens

           

          Aggregate Bin.gif

           

          Hopefully you understand why it's doing this...let me know if not

           

          but it sounds like here that you want the Cost/Pick and the resulting bins based on each Date/ID...so LoDs to the rescue!

           

          If I use a LoD on your Cost/Pick Tableau knows how we want this run, and then the results are returned (at row level) to each ID/Day combination...so it's like a real dimension (at this point you can just use Tableau's bin function). However I have just used the formula method

          [cost/pick - by Day/ID LoD]

          {FIXED [Sale Date], [Final ID]: [cost/pick]}

           

          and then the Bin is

          [cost/pick - Bins On LoD]

          INT([cost/pick - by Day/ID LoD]/[Bin Size])*[Bin Size]

           

          and then we can use this to create your charts

           

          Hope that helps, and makes sense...let me know if not

          2 of 2 people found this helpful
          • 2. Re: Create Bins from Aggregate value
            Puja Doona

            Hi Simon,

             

            Thanks for the clear explanation. It really helped me understand the concept of LOD as well

             

            On the problem - it won't exactly solve the problem due to the use of INT ().  I need to have bins in more narrow range (with decimals - say $5, $5.5, $6 OR $5, $5.25, $5.5 and $6 with gap of 50c or 25c) as that is an important part of reporting. Rounding because of INT() usage, would not allow me to use this explanation.

             

            Any alternate solution to this problem?

            • 3. Re: Create Bins from Aggregate value
              Simon Runc

              So you can adapt the formula to multiply by 10, do the INT binning and then re-dividing by 10...But once we have the Aggregated value as an LoD (so the results of the aggregate are returned at row level) we may as well just use the Bin feature in Tableau

               

              I've added this [cost/pick - by Day/ID LoD (bin)] here, controlled by a new Parameter (Bin Size Float)

              • 4. Re: Create Bins from Aggregate value
                Puja Doona

                Hi Simon,

                 

                Thanks a lot though I didn't quite follow the "multiple by 10 ...dividing by 10" bit I think the using the Bin Size Float parameter did the trick.

                 

                I faced another issue though. When working on larger data set, a lot of rows had NULL in the cost/pick column (but other KPIs were populated). I think that did throw the LOD calc off.

                 

                Any idea how to tackle that when working on larger data sets?

                • 5. Re: Create Bins from Aggregate value
                  Simon Runc

                  Cool...well if it's working, let not complicate things with the *10 and /10!!

                   

                  So you probably just want to wrap your cost / pick calculation in a ZN...so that if either cost is NULL, or Pick = 0, then it will return a Zero and not NULL, and that way it'll be put into the first zero - X.X bin

                   

                  ZN(SUM([Cost Of Pick])/SUM([Times Picked]))

                  • 6. Re: Create Bins from Aggregate value
                    Puja Doona

                    Hi Simon, seems like there is some issue while working off excel files vs pulling directly from database source. What's happening is that the bin in the calculated field using INT() formula is actually exceeding the defined bin size range in the parameter. Not sure why that is happening. However, when I download the same data on excel and import in to tableau, everything seems fine. Not sure what's going on. Let me understand this fully and update the thread.

                    • 7. Re: Create Bins from Aggregate value
                      Simon Runc

                      Very strange!

                       

                      If it's of any help, here is the way to get Bins (on Floats) using the formula method (example attached)

                       

                      (INT(([cost/pick]*10)/([Bin Size (Float)]*10))*([Bin Size (Float)]*10))

                      /10

                       

                      We're doing the Bin(ing) on 10 times the numbers, using INTs and then dividing back through by 10 to get the final Bin on a floated value.