5 Replies Latest reply on Nov 15, 2017 12:53 AM by Tyler Garrett

    Learning the tool; is this possible?

    Zachary White

      OK, so here's the long and the short of my question: can I build a worksheet that creates dots to equal percentages from 0-100 percent?

       

      Essentially a bar graph, but with dots.

       

      For example: no dots would be zero, two would be 40 percent, five would be 100 percent.

      dots.jpg

        • 1. Re: Learning the tool; is this possible?
          Jim Dehner

          Hi

          Welcome to Tableau

          Yes it is possible to do

          See the attached t10.2 workbook

          first create your formula

          select a shape viz

          then place it on the color tile and the shape tile

           

           

           

          set all shapes to solid round (use edit shape to do that)

           

           

           

           

           

          Edit color by - select color scheme then open the advanced editor and select the number of segments

           

          That should do it

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Learning the tool; is this possible?
            Zachary White

            Hey Jim Dehner,

             

            Thanks for the response; however, I'm not sure I understand.

             

            I'm looking for multiple dots to replace a single data point in a bar chart. What you shared looks like it has multiple data points being used to create multiple dots.

             

            Example.jpg

             

            Additionally, I'm not sure how you opened that data table to create a formula.

             

            Thanks for the warm welcome, and I appreciate the help. Sorry if I'm missing your meaning.

            1 of 1 people found this helpful
            • 3. Re: Learning the tool; is this possible?
              Catherine Brown

              Zachary,

              You may be able to trick it into doing a visual like that for you, but it would not be straight forward.  I imagine it would require some heavy use of the dual axis functionality, essentially overlaying single dots (operating each like a single bar), on top of each other.  Each equally spaced from the next, and representing whatever next threshold you set for the visual.  I'll think on it a bit more and get back with out, I may be over thinking it.

               

              Catherine

              • 4. Re: Learning the tool; is this possible?
                Catherine Brown

                This isn't perfect, but I think it closer to what you are looking for.  I didn't have to mess with dual axes at all.  It would just need a little cleanup and tweaking (and an additional threshold level).

                • 5. Re: Learning the tool; is this possible?
                  Tyler Garrett

                  There's a few ways to do it, but this entirely depends on the data architecture in your ecosystem.

                   

                  If you're getting a percentage, then you can quickly utilize native features.

                   

                  For example: utilizing BINS will suffice.

                  Screen Shot 2017-11-15 at 2.09.17 AM.png

                   

                  Now please note a couple of things:

                   

                  Create Bins from a Continuous Measure

                  Note: You can bin data only for relational data sources. This feature is not supported for cube (multidimensional) data sources, though you can write a calculation that takes cube cell results and bins them. For example,

                  str((INT([Internet Sales Amount]/1000)) * 1000).

                   

                  Above is an option, but let's dive into the BIN per percentages, per the screenshot above.

                   

                  Starting with the dimensions and viz. Utilizing .02 bin size.

                  Screen Shot 2017-11-15 at 2.13.30 AM.png

                   

                  Here's the datasource, which provides a percentage out of the gate, which is averaging on the visualization.

                  Screen Shot 2017-11-15 at 2.14.04 AM.png

                   

                  Next let's look at the MAKEUP of our marks card.

                   

                  Note the placement.

                  Screen Shot 2017-11-15 at 2.15.05 AM.png

                   

                  Now take a peak with me at the BIN screen.

                   

                  The drop down gives you an opp to create a parameter. Do that.

                  Screen Shot 2017-11-15 at 2.15.42 AM.png

                  Take a peak-a-boo at the parameter settings. Sure you didn't ask for it, but boy your users are going to be impressed.

                   

                  Because the granularity of 20% is a bit. Well, it's BIG, and side by side comparison could become challenging.

                   

                  It's good to add a parameter in these fields because you don't need to show your user it, you can maintain it from ONE viz on your dashboard and not stress about hardcoding the bin again.

                   

                  Key notes:

                  1. FLOAT, offers decimals. Which is a percentage.
                  2. An INT will not help you here, nor a string.
                  3. Int is ROUND numbers above - with no decimal place.
                  4. String is for characters. (names, zipcodes, phone numbers, regions)
                  5. Minimum .01 offers 1%.
                  6. Maximum 1 offers 100% max. (please edit this if you're reporting of percentages that can be above or below.
                  7. Step size = What you want the user to be able to pick, or what you want yourself to pick. (users dont see it unless your bring it to the dashboard!)
                  8. click OKAY, and name it something short, not a sentence, that's going to make your calcs impossible to read in the future.

                   

                  Screen Shot 2017-11-15 at 2.16.18 AM.png

                   

                  Okay let's take a peak at the BIN being 1% bucket sizes.

                  Screen Shot 2017-11-15 at 2.20.46 AM.png

                   

                  Oh snap, we are on to something. The more I mess with it, the more the values changed. Might be on the right path here.

                   

                  When I first saw this operation long ago, everyone seemed to utilize a form of this calculation

                  str((INT([Internet Sales Amount]/1000)) * 1000)

                  To dimensionalize aggregations. (i know it's not a word, but who made up vizzes? cool i made up dimensionalize lol)

                   

                  I truly feel BINs have come a long way, so let's keep at it.

                   

                  My 2cents: Whenever positioned - I always solved it on the data and bubbled up things easy to play with in Tableau. Less is more, and Tableau likes lean, fast, quick, and so do executives.

                   

                  Back to it. Without diving to deep tonight - let's see how I trick Tableau to label this.

                  Screen Shot 2017-11-15 at 2.25.35 AM.png

                  Went with middle right alignment, otherwise it's all over, and Mark min/max, scope CELL.

                   

                  Play with Bins if the data is bringing back percentages. The numbers here are a bit wonky and I'd probably lean -str((INT([Internet Sales Amount]/1000)) * 1000)

                   

                  ------------------

                   

                  Otherwise, table calculation time.

                   

                  And if you can MOCK up the 'data need' in excel, I bet you can make it a lot easier on yourself to fix it there, pass it to your architect, or just make it a part of your workflow.

                   

                  Getting deeper into these desktop calculations may not be the best course of action unless you know the functions/logical functions really well.

                   

                  Sorry I'm not able to hit the hammer on the head - unless I'm looking directly at your data, and that's probably not something you should share here.

                   

                  If you can mock it up, I bet most experts here will solve it quickly.

                   

                  Build a dumby data set. Then let these tableau desktop jedi/ninja's show you the way.

                   

                  If it can be worked out on the data side. I'd start there first. Just a personal preference having done SQL for a long time. Because the data I'm utilizing offers a heavy granularity, if you can group by exactly what you need, and you're just getting a flat percentage. I believe you will have a lot easier time. Hopefully you can knock out all next steps from this point forward!

                   

                  Hope this finds you well. Had a late meeting with overseas partner and just noticed this question in my inbox.

                   

                  For some strange reason i feel it could go down like:

                   

                  if x>.2

                  then 1

                  elseif x>.4

                  then 2

                  elseif x>.6

                  then 3

                  elseif x>.8

                  then 4

                  elseif x<1

                  then 5

                  end

                   

                  Note this calculation ABOVE doesn't take into account the off chance your data has a NEGATIVE or >1...

                   

                  If it doesn't go over or under and it's a true percentage out of 100, or percent of total. See BELOW

                   

                  Then....

                   

                  if x>.2

                  then 1

                  elseif x>.4

                  then 2

                  elseif x>.6

                  then 3

                  elseif x>.8

                  then 4

                  else 5

                  end

                   

                  We are reducing the loop.

                   

                  As every loop is going to be repeated every row of data...

                   

                  So if you have 200million rows. That's 200 * X loops. Every loop will process per CELL or at a row level.

                   

                  Please don't throw this down the pipeline on a LIVE data set, extract it, if it's not small - understand sometimes it's best to show your data people this nifty thing, and they can help you isolate a better architecture, to peel out this calculation, just the off chance I'm telling you something and you're running it on a production database, it's smart to ask for a dev environment, or TOP 1000. Hard to say what you're doing with what I have available, but I just assume others will help with the rest once you share the data architecture mock up.

                   

                  Here's a previous blog that might be talking about this from 2015 - How to calculate percent within a histogram bin

                   

                  Best and have fun!

                  Tyler

                  Dev3lop

                  1 of 1 people found this helpful