3 Replies Latest reply on Nov 19, 2016 6:42 AM by Saravanan S

    Bin Analysis

    Saravanan S

      Hello,

       

      I have scenario which need to be addressed.

       

      I have items, sub items and their respective combination sales count for each hour in a day.

       

      I need to aggregate the items and sub items count into various bins.

       

      Sample Data

                                                                 Hour

      Item        Sub Item         1           2         3          4    

      Item 1     Sub Item 1      6           15

                     Sub Item 2      35          4

      Item 2     Sub Item 1      100       50

                     Sub Item 2      15         45

       

      Requirement:

                                                             Hour

      Bins                                 1          2          3

       

      0-20                                 2          2

      20-40                               1          0

      40-100                             1          2

       

      For ex: 0-20 Bin - if the count of sum of item and sub items combination which have been sold in first hour then group in 0-20 Bin

       

      Likewise i have group in different bins

       

      Thanks

      Saravan

        • 1. Re: Bin Analysis

          Simon Runc has answered some tough bin questions in the past. Any help?

           

          -Diego

          • 2. Re: Bin Analysis
            Simon Runc

            Thanks for the ping Diego...

             

            Hi Saravanan,

             

            So I've done this a couple of ways (one using Tableau default bins, and the other using "Human Readable" bins)...

             

            So the first thing we want is a Row Level measure for the SUM([Sales Count]) for each Item/Sub-Item/Hour combination (it looks like this is the grain of your data here, but just in case your "real world" situation has a finer grain, I've created an LoD to force the SUM at the level we want)

             

            [Sales Count Per Item/SubItem/Hour - LoD]

            {FIXED [Item],[Sub Item],[Hour]: SUM([Sale Count])}

             

            Once we have this, we can use the Tableau Bin function to create our Bins (I've created a parameter, so you can control the bin sizes dynamically...and is currently set to 10)

             

             

            Alternatively, I have also created "Human Readable Bins", using a calculated field...just copy the formula here  Human Readable Row Level Dynamic Bins (and change the fields to you ones)

             

            STR(

                IF (

                    (ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]))

                    < [Sales Count Per Item/SubItem/Hour - LoD]

                    THEN ((ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) + 1)

                    ELSE ((ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) - [Sales Count Per Item/SubItem - LoD Bin Size] + 1)

                    END

                    )

            + ' to ' +

            STR(

                IF (

                    (ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]))

                    < [Sales Count Per Item/SubItem/Hour - LoD]

                    THEN (ROUND([Sales Count Per Item/SubItem/Hour - LoD]/ [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size]) + [Sales Count Per Item/SubItem - LoD Bin Size]

                    ELSE (ROUND([Sales Count Per Item/SubItem/Hour - LoD] / [Sales Count Per Item/SubItem - LoD Bin Size]) * [Sales Count Per Item/SubItem - LoD Bin Size])

                    END

                    )

             

             

            The next part is to combine the Item and Sub-Item fields, so I have a unique value for each Item/Sub-Item combination

            [Item + SubItem]

            [Item]+' '+[Sub Item]

             

            and then I can arrange the pills as required, and use a COUNTD on [Item + SubItem]

             

            and I'd also filtered out the NULL sales count

             

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

            • 3. Re: Bin Analysis
              Saravanan S

              Thanks Simon. Let me check the solution and revert.