11 Replies Latest reply on May 1, 2013 9:32 AM by Jonathan Drummey

    Fixed Bins Sum

    Kimball High

      I used the tutorial on THIS post to fix my bins dynamically at 10. Now I want to avg all the agents in each of the 10 bins so I can compare them.

      See screenshot of current view:

      10 Bins.JPG

      I would like this visualization to eventually look like this:

      Bins Graph.jpg


      Your help is gratefully appreciated.



        • 1. Re: Fixed Bins Sum
          Daniel Leventhal

          I don't know the right answer, and I'm sure there are better solutions, but here's something that comes to mind. You can create a per-pane reference band that represents the average per bin. The attached workbook has that for the example you linked to. I also added some transparency on the marks so the average band shows through.


          bin avg.PNG

          • 2. Re: Fixed Bins Sum
            Kimball High

            That keeps the structure of the state under each BIN which i'd like to remove to keep just the BIN (grey bars).


            Thank you for giving it a go Daniel.

            • 3. Re: Fixed Bins Sum
              Kimball High

              Let's see if we can revisit this question WITH REAL DATA (attached) and see if any of you super guru's can assist with a repeatable solution. I look forward to seeing your methods to tackle the challenge.

              • 4. Re: Fixed Bins Sum
                Shawn Wallwork

                Great that you posted some data, but it would be even more helpful if you built the view that you posted in your first post (or something similar). Then we won't have to guess at what you want to bin.



                1 of 1 people found this helpful
                • 5. Re: Fixed Bins Sum
                  Kimball High

                  Thank you Shawn, I've now uploaded data with the dynamic bins for Location by AVG(Overall Sat#)




                  • 6. Re: Fixed Bins Sum
                    Shawn Wallwork

                    Kimball, sorry no luck. I don't think you can get there using your original technique, because as soon as you take the location out of the viz you break the formulas. May someone else has more experience with binning.



                    • 7. Re: Fixed Bins Sum
                      Jonathan Drummey

                      I've attached a solution that uses DIY partitioning by table calcs, here's an explanation:


                      - The table calcs were set up with a Compute Using of Table (Down), which is a form of relative addressing. Relative addressing is relative to pill arrangement in the view. The alternative is fixed addressing, i.e. choosing specific dimensions for addressing and partitioning. In this case, setting the table calcs to use Location for the Compute Using works, and then we can move Location to the Level of Detail Shelf.


                      - Ideally, we'd be able to have a new calculation that could do a WINDOW_AVG() with a Compute Using on Location and partition on the table calc Dynamic Bins, but Tableau does not have that feature, we can only partition on dimensions and discrete aggregates. However, via the use of LOOKUP() and PREVIOUS_VALUE() we can create DIY partitioning calcs, where each calc is made aware of the partition.


                      The eponymously named Running Sum of Average Overall Sat# uses those functions, restarting for each new Dynamic Bin. Then the Avg for Bin calc uses the Size and Size of each Bin calcs to get the denominator and generates the average Overall Sat# for each Dynamic Bin, and makes sure to use the appropriate denominator where the last bin size may be smaller than rest of the group. For example, in the data where there are 10 bins, all the bins but the last have 165 rows and the last bin has 161 rows. The Avg for Bin calc only returns values for the last row in each bin (Null for everything else), and is use to generate the chart. The calc is also used on the Filters Shelf to filter for non-Null values so Tableau only draws as many marks as bins.


                      [added after original post]

                      - I also added a few optimizations: Since the Size and Size of Each Bin calcs have the same value for every value in the partition (the table), I wrapped them in PREVIOUS_VALUE() so they are only computed once for each partition. Second, I changed the Size of Each Bin calc to actually use the Size calculated field, the KB article incorrectly has us generate a Size calc but then doesn't use it.


                      Finally, on my machine Tableau takes about 20 seconds to connect to the data source, that part of the processing would be a lot faster if you used an extract.



                      • 8. Re: Fixed Bins Sum
                        Shawn Wallwork

                        Nicely done Jonathan! "Eponymously" is now my word for the day.




                        PS: For those non-coder types like me, two notes

                             1) != is same as <> 

                             2) == is same as = (in Tableau)

                        • 9. Re: Fixed Bins Sum
                          Jonathan Drummey

                          One more note: I did some further testing on a large data set and it seems like there's no real difference between PREVIOUS_VALUE(SIZE()) and SIZE(), I'm guessing that SIZE() is evaluated only once for the partition. So you can skip that optimization step.



                          • 10. Re: Fixed Bins Sum
                            Kimball High



                            This is very smart, and understandably easy to replicate. I like how the Avg for Bin formula works to display just the one value which creates the visualization. Your optimization recommendations are also very helpful. We typically use an extract as our data sets can be much larger than this demo set. And waiting for them requires the patience of Job.


                            Regards to SIZE() and PREVIOUS_VALUE(SIZE()) I can continue to use SIZE().


                            Thank you again for taking the time to assist with this useful solution.

                            • 11. Re: Fixed Bins Sum
                              Jonathan Drummey

                              You're welcome!