5 Replies Latest reply on Jan 6, 2017 12:40 AM by Norbert Maijoor

    creating bins for variable bound between 0 and 1

    Michael Reinhard

      0down votefavorite

       

       

      I have just started using Tableau and I have run into a problem. I want to create a histogram of the percentage of loans that have not been paid back. I created a variable called 'Delinquent num' coding the loans that have not been paid back as 1 and all others as 0. Then I calculated the percentage of loans that have been paid back (Delinquent pct) as (SUM([Delinquent num])/COUNT([Delinquent num]))*100.

      I entered this variable on the sheet as the Rows variable and got

      Screen Shot 2017-01-02 at 11.00.17 AM.png

      That is fine; about 18% of the loans go bad. But now I would like to make a histogram. That choice is not highlighted in the "show me" menu. I tried to create a 'bins' variable from the pull down menu from the variable itself but the option to create bins is not offered for the variable I created and I don't know why. Creating bins is offered as a choice for the Delinquent num but since everything is either a 0 or a 1 in that variable a histogram would not be terrible useful. Any suggestions?

      Here is a link to the public notebook if that would be useful.

       

      https://public.tableau.com/views/project3_working/pctDelinquent?:embed=y&:display_count=yes

        • 1. Re: creating bins for variable bound between 0 and 1
          Norbert Maijoor

          Hi Michael,

           

          Not sure....but find my approach as reference below and stored in attached workbook version 10.1

           

          a.

           

          b. Delinquent num:

          IF [Loan Status]="Charged Off"

          or[Loan Status]="Default"

          or[Loan Status]="Does not meet the credit policy. Status:Fully Paid"

          THEN [Number of Records] END

           

          2 of 2 people found this helpful
          • 2. Re: creating bins for variable bound between 0 and 1
            Michael Reinhard

            Thanks Norbert. I actually had an answer from Stackoverflow before Tableau approved posting my question, but your answer really helped me understand what is going on. Thanks!

             

            Actually, I think there is something un-"Tableauic" about my whole approach. I used to create dummy variables all the time in research but it does not seem to be very common in Tableau. It makes me wonder if there isn't a completely different approach possible with Tableau that I am over looking?

            • 3. Re: creating bins for variable bound between 0 and 1
              Jonathan Drummey

              Hi Michael,

               

              I've been trying to figure out succinct ways to describe this difference over the last couple of years, here's another attempt:

               

              When we're working with Excel or Python/R/SAS/SPSS etc. the workflow is to start out with a data set, then do a transformation of some sort such as creating a derived variable, using a SUMIF() function to do an aggregation, etc. that creates a new worksheet/table/data set, do 0...N more transformations where each transformation creates a new worksheet/table/data set, then finally graph the results.

               

              Tableau has an "all at once" model where we start out with the Tableau data source (which might be a combination of multiple tables from multiple databases/worksheets) and when the view updates by dragging and dropping a pill Tableau queries the sources, does computations at up to four different levels (record-level, aggregate, table calculation, and post-aggregate) and potentially many levels of detail once we start getting into top & conditional filters, computed Sets, context filters, Level of Detail expressions, etc. and (finally) draws the view. Tableau has an internal logic around this, key elements are understanding the levels of calculation, dimensions & measures, order of operations, and the levels of detail. This all works fantastically in the background as we are dragging and dropping pills right up to the point where the way we've been conceiving of building out a view doesn't line up with how Tableau does and we get stuck because the details of what Tableau is doing are obscured (or completely hidden) from our view & discovery.

               

              Norbert's solution identified the dimension needed on the vizLOD (the bin of the loan amount) and used that, while still using the delinquent num as a measure.

               

              Jonathan

              2 of 2 people found this helpful
              • 4. Re: creating bins for variable bound between 0 and 1
                Michael Reinhard

                Thanks, Jonathan. That really helps me put it into perspective. I have found that getting away from the "Excel or Python/R/SAS/SPSS" way of doing things has actually helped my intuition and understanding. I really appreciate the way that Tableau users are willing to dive into the intuition behind how things work in Tableau, too.

                • 5. Re: creating bins for variable bound between 0 and 1
                  Norbert Maijoor

                  Goodmorning Jonathan,

                   

                  Once again thanks for "stepping in" and taking a "step back" and elaborating on "behind the scene's" concerning the Technic and way of working of Tableau. Appreciated!:)