7 Replies Latest reply on Aug 9, 2017 12:20 PM by Carol Ramey

    Something Strange About My Dataset

    Carol Ramey

      I'm new to Tableau and I feel like I might need to find a really good therapist. It is not going well.


      I'm starting with simple line graphs, but they aren't working. The examples I find all use sales data. Yeah sales! My data are all boolean and the results I want to show are mostly percents. I think Tableau is good at using lots of raw numerical data and generating formulas from that, where the percents are built into the graph. I have no raw numerical data. Strange, huh?


      Since Tableau seems super confused about what to do with boolean raw data, I created formulas in Excel to calculate percentages and I'm just trying to plot the percentages on a simple line chart and that doesn't work either. This is an example below. The numbers are pulled from the simplest of Excel files with one row per year with two percentages per row for two measures. I have to use Average for the measure, even though I'm not averaging anything, there is only one value for each year/measure type. Now I can't get the text "Avg." out of the legend. Any ideas?


      I'm having trouble with all of my charts, most which seem to come back to the idea that my raw data are booleans and not numbers. Do you have general ideas for how much trouble I'm in for?


      Any ideas are appreciated.


      Carol in Texas



      Screen Shot 2017-08-08 at 8.44.59 PM.png




      I have added a packaged workbook. The sheet I'm working on now is Sheet 12. I have a filter for High School. My columns is School Year. I have two measures: 1+ CS Course and CS Req Met, which report on boolean fields in 1 and 0 format and using SUM on those fields to get the count. The CS Req Met line is correct, but the 1+CS Course line isn't. The values, according to the raw spreadsheet, using filters and formulas to confirm, are:

      Screen Shot 2017-08-09 at 12.24.05 PM.png

      Any ideas what I'm missing?


      Thank you.




      Message was edited by: Carol Ramey

        • 1. Re: Something Strange About My Dataset
          Tom W

          To get any real insight we need to see a sample of your data - Packaged workbooks: when, why, how


          Working with booleans is fine, you're just going to have to create some calculations within Tableau to actually aggregate those.

          1 of 1 people found this helpful
          • 2. Re: Something Strange About My Dataset
            Catherine Brown

            Based on your graph, I'm guessing you are either in K-12 or higher ed?  There are tons of higher ed tableau users, and a growing group of K-12 users.  I work in K-12, and most of our data is boolean.  It really isn't too tricky, it is just a question of learning how to aggregate it properly.  Not really much different than doing the work in excel. You should look around in the Tableau communities, there are groups on here specifically for those industries, and some have local meetings every once and a while (usually Government/Education together).  I know we have them in Texas, because I've been to them both in Houston and Austin/San Antonio.  There are also tracks specifically for education at the national conference.  Your Tableau representative may be able to put you in touch with some other local users.  Ours is great!


            Back to your question:

            Say you have a list of students and know whether or not they participated in a sport.  Just a list of ID's and a column of 1/0 or Y/N for participation.  Calculation wise, working with 1/0 is a little less annoying than Y/N.  So that's a first step.  Converting your Y/N to 1/0.  Not bad to do that just write a calculation that says 'If [Field] = 'Y' then 1 else 0 end', or some permutation of that.


            Now, if you sum that 1/0 field.  You'll have a count of students that participated in a sport.  Or you can count the 1's.  Either way works.


            Then you can create a formula to calculate the percent.  Something like sum([Field])/count([ID]  or sum([Field])/sum([Number of Records]).  Then format as a percent.


            Hope this helps,

            Catherine also in Texas

            1 of 1 people found this helpful
            • 3. Re: Something Strange About My Dataset
              Carol Ramey

              Thank you Tom!

              • 4. Re: Something Strange About My Dataset
                Carol Ramey

                Thank you Catherine!


                I created the 1/0 items and I'm working with the raw counts first. I added info to the post with a packaged workbook. The SUM of the 1s is working for one of my measures, but not the other, which makes much less sense than if if didn't work at all. Do you have an idea?


                Also, thank you for the tips about the Education and Local forums. That will be very helpful! I work at UT on research for K-12.


                Thank you again for your help.




                • 5. Re: Something Strange About My Dataset
                  Tom W

                  You noted: CS Req Met line is correct


                  However the data in your Tableau Report does not match the data you've just provided in that screenshot. Are you sure you're doing your comparison correctly?


                  With that being said, the problem is your joins. If I change your joins to this;

                  This is what I get:


                  The number still doesn't match your Excel number, but I don't believe your excel number is actually correct based on what I'm seeing here.


                  Regarding the joins - if you set up inner joins between each table / source, data is going to be eliminated if there's no matching record in the right table for the record being joined from the left. You should probably be using LEFT joins if this is not the behavior you want.



                  • 6. Re: Something Strange About My Dataset
                    Tom W

                    Additional problems with your joins,

                    In your Excel source file CSTX_Data_2016 on the 'School+Year' tab you have a field in CC called 'CSTEM HS'. I'm sure you're filtering on this then counting column CA 'CSTEM INT 1+ CS Course'  i.e. it's 384 for School Year 2012.


                    Part of your problem is you aren't doing an apples to apples comparison. You're loading it into Tableau then applying a filter on the field 'Cstem Hs' on the 'CSTEM School' tab and those values aren't the same! Look at this:


                    So, it really boils down to a few things:

                    1. Understand your joins
                    2. Understand your fields and filters in order to make a true comparison between your source and Tableau.


                    It doesn't look like you're far off!

                    • 7. Re: Something Strange About My Dataset
                      Carol Ramey

                      Oh, my, this is very helpful! Thank you for the time you took to write this out!


                      Thank you, thank you!