11 Replies Latest reply on Apr 18, 2018 11:57 AM by Stephen Groff

    Some basic questions from a very new user: filters, sheets, calcs

    Stephen Groff

      Greetings.  Even though I have been trying to use Tableau for 2 weeks now I still have very basic questions.

       

      First off, my dataset has 160 columns that contain every piece of information about our assembly process... there is no other dataset, just a big jumbled mess of BS.

       

      1.  On sheet 1 I have filtered my data perfectly to represent the number of... R2D2 droids we have built.  This sheet doesn't contain any calculations other than the measures auto-produced.

       

      2.  On sheet 2 I have filtered my data perfectly to represent the number of...  Defects associated with all unique R2D2 serial numbers.  This sheet doesn't contain any calculations other than the measures auto-produced.

       

      Arriving at these number requires different filters, hence, the 2 different sheets.  But how in name of Jesus' whiskey bottle does one take those to numbers and use them in a calculation?

       

      # of R2D2's built / # of R2D2 defects = # of defects per R2D2 built.

       

      I really want to convey how upset I am by using a lot of profanity...  I hate Tableau!  I hate it.  This is so easy in Excel and Tableau seems to have turned something incredible easy into something convoluted.  I want to take my number of machines I built and divide that by the number of defects.  How much easier could this be?  It's only 6:45am and I already hate my life today.

       

      EDIT:

      ************Okay... so 40 views and no answers... let me give it to you a different way.  Look at "Meeting1" attachment.  I created 2 separate and basic SQL queries that sort of answer the above question.  Problem is... my join is messed up.  It doesn't seem to matter how I join the data, or what join clause I use, I always end up with NULL values (machines) when I start to compute my DPU by date.  Year/Month.  I believed this was the best way to compute my DPU in the beginning (pertaining to the above question), but I still cannot get the data to give me the output I need because the DPU is wrong (not computing the NULL values).  Ideas?

        • 1. Re: Some basic questions from a very new user: filters, sheets, calcs
          Zhouyi Zhang

          Hi, Stephen

           

          Is this the result you are expected? if yes, please find attached workbook

           

          ZZ

          • 2. Re: Some basic questions from a very new user: filters, sheets, calcs
            Stephen Groff

            Let me doing some work with what you sent.  I appreciate you looking into this.  See my EDIT to my original message.  Many thanks.  I'll reply soon...

            • 3. Re: Some basic questions from a very new user: filters, sheets, calcs
              Mark Holtz

              Come back down off the ledge, Stephen! We're here to help. =)

               

              If you want to create calculations that use 2 "differently filtered measures" in the same view, you'll have to employ a construct that you will use often in Tableau. Percentage calculations often work like this. I always try to keep in mind that if you put a dimension (or a measure) in as a FILTER on the view, it disallows you from being able to access the records in your data set that fail to pass the filter criteria. If you have set A as a subset of set B, you can't very well FILTER to A because now B will be reduced to only A.

               

              The solution:

              Create 1 (or more) calculated fields that will give you your numerator and denominator.

              Effectively you "filter" what contributes to each measure in the calculation defining the measure instead of in the filter shelf.

               

              Calculated Field 1 "# of R2D2s with defects":

              IF [CriteriaField1] = 'Criteria to count as built' AND [CriteriaField2] = 'logic to count as defect' //there may be more than one "test" for your criteria

              THEN 1 //because you wanted count, I'm assuming each record is the count you want, but if a record represents more than one count, use the [MeasureField] that gives the appropriate count

              ELSE null //this is not required, but I like it to be thorough and explicit. NULL values won't ruin a fraction if the denominator ever ends up having no values--unlike 0 would do

              END

               

              Calculated Field 2 "# of R2D2s built":

              IF [CriteriaField1] = 'Criteria to count as built' //there may be more than one "test" for your criteria

              THEN 1 //because you wanted count, I'm assuming each record is the count you want, but if a record represents more than one count, use the [MeasureField] that gives the appropriate count

              ELSE null //this is not required, but I like it to be thorough and explicit. NULL values won't ruin a fraction if the denominator ever ends up having no values--unlike 0 would do

              END

               

              Calculated Field 2 might be optional for you because it could just be "all records you allow in the view" i.e., set B from my "A is a subset of B" example.

              In that case, you might be able to use [Number of Records] or a pure measure field from your data.

               

               

              Now you can create a calculated field to give yourself the fraction:

              SUM([Number of Records]) / SUM([Calculated Field 1] )

              This should give you "Count of R2D2s built per Defect."

               

              Note that you must aggregate and THEN divide so that the calculation takes place in aggregate.

              If you don't, you'll get SUM([Number of Records]/[Calculated Field 1]) which will likely be a nonsense result.

              • 4. Re: Some basic questions from a very new user: filters, sheets, calcs
                Stephen Groff

                Okay... I have to learn this... I want to learn this... and I'm in the den with the wolves. 

                 

                So in dummy terms, what you are essentially telling me to do is create a calculation using an IF statement outlining my parameters, and if these parameters are met, THEN 1, ELSE NULL

                 

                Then I can count the 1s and get my number of machines or defects.

                 

                Then I take those calculations and simply divide them to get my defects per unit.

                 

                I guess I better get to work learning how to write IF statements in the syntax Tableau wants to see. 

                 

                Much obliged Mark.  I'll see what I can come up with.

                • 5. Re: Some basic questions from a very new user: filters, sheets, calcs
                  Stephen Groff

                  Mark,

                   

                  I've done a quick if statement based on the filter parameters I want to use to find the number of defects, however, I've run into a problem I cannot define.

                   

                      IF ISNULL([Dispo Cd])

                          AND [Prototype Ind]='N'

                          AND [Ext Cpy Stat]<>'D'

                          AND [Fac Prod Fam Cd]='ACOM' OR [Fac Prod Fam Cd]='SCOM' OR [Fac Prod Fam Cd]='LAP' OR [Fac Prod Fam Cd]='RM' OR [Fac Prod Fam Cd]='SCRD'

                          AND [Cuz Area Id]<>'0_DEFECTS'

                          AND NOT STARTSWITH([Event Desc],'0')

                          AND [Disc Area Id]<>'501' OR [Disc Area Id]<>'525' OR [Disc Area Id]<>'600' OR [Disc Area Id]<>'700' OR [Disc Area Id]<>'701' OR [Disc Area Id]<>'702' OR [Disc Area Id]<>'703' OR [Disc Area Id]<>'704' OR [Disc Area Id]<>'705' OR [Disc Area Id]<>'706' OR [Disc Area Id]<>'800' OR [Disc Area Id]<>'900'

                          AND [Quality Velocity]='Q'

                      THEN 1

                      ELSE NULL

                  END

                   

                   

                  The "Number of Records" adds up to 59,237

                  When I add in this calculation... nothing changes, the total is still 59,237.  Even when I change the aggregation to COUNT.  I can tell that the calculation is incomplete, but I'm not certain where.  It seems I'm defining parameters, but not telling the calculation what dimension/value I want it to look at.  The number I'm looking for will be just over 30,600

                   

                  Ideas?

                  • 6. Re: Some basic questions from a very new user: filters, sheets, calcs
                    Mark Holtz

                    Your first IF statement is a doozy!

                    When using ORs and ANDs together, you almost always need to employ some parentheses--if you are being selective/additive with the ORs instead of exclusive/subtractive...

                    When using <> (being exlusive/subtractive), you want to use AND instead of OR because you are specifying mutually exclusive criteria.

                     

                        IF ISNULL([Dispo Cd])

                            AND [Prototype Ind]='N'

                            AND [Ext Cpy Stat]<>'D'

                            AND ([Fac Prod Fam Cd]='ACOM' OR [Fac Prod Fam Cd]='SCOM' OR [Fac Prod Fam Cd]='LAP' OR [Fac Prod Fam Cd]='RM' OR [Fac Prod Fam Cd]='SCRD')

                            AND [Cuz Area Id]<>'0_DEFECTS'

                            AND NOT STARTSWITH([Event Desc],'0')

                            AND [Disc Area Id]<>'501' AND [Disc Area Id]<>'525' AND [Disc Area Id]<>'600' AND [Disc Area Id]<>'700' AND [Disc Area Id]<>'701' AND [Disc Area Id]<>'702' AND [Disc Area Id]<>'703' AND [Disc Area Id]<>'704' AND [Disc Area Id]<>'705' AND [Disc Area Id]<>'706' AND [Disc Area Id]<>'800' AND [Disc Area Id]<>'900'

                            AND [Quality Velocity]='Q'

                        THEN 1

                        ELSE NULL

                    END

                    1 of 1 people found this helpful
                    • 7. Re: Some basic questions from a very new user: filters, sheets, calcs
                      Stephen Groff

                      Ahh yes... the parenthesis!  Didn't even cross my mind.  As far as the AND OR... I wasn't quite sure which was the correct one to use.  But, this gave me the exact number of defects I wanted to see.

                       

                      Now I just need to do the same thing to get my # of machines.

                       

                      Then divide one by the other to get my Defects Per Unit (DPU).  Thanks for talking me off the ledge.  I really appreciate the help.  You just gave me the stepping stone I needed to begin... everything.

                       

                      Appreciated.

                       

                      Steve

                      • 8. Re: Some basic questions from a very new user: filters, sheets, calcs
                        Mark Holtz

                        Happy to help.

                        I mostly use the same things from my bag 'o tricks all the time.

                         

                        Just a note, I recommend using SUM instead of COUNT.

                        In this case, they're equivalent, but if you had used 0 instead of NULL in the ELSE clause, COUNT would still increment whereas SUM would not.

                        This also always works even if you to swap in measure fields from your data instead of 1 in the "THEN 1".

                        1 of 1 people found this helpful
                        • 9. Re: Some basic questions from a very new user: filters, sheets, calcs
                          Stephen Groff

                          Mark, could I essentially use these parameters to drill down a dimension.. something like:

                           

                          COUNTD([Serial #])   

                           

                          IF ISNULL([Dispo Cd])

                                  AND [Prototype Ind]='N'

                                  AND [Ext Cpy Stat]<>'D'

                                  AND ([Fac Prod Fam Cd]='ACOM' OR [Fac Prod Fam Cd]='SCOM' OR [Fac Prod Fam Cd]='LAP' OR [Fac Prod Fam Cd]='RM' OR [Fac Prod Fam Cd]='SCRD')

                                  AND [Cuz Area Id]<>'0_DEFECTS'

                                  AND NOT STARTSWITH([Event Desc],'0')

                                  AND [Disc Area Id]<>'501' AND [Disc Area Id]<>'525' AND [Disc Area Id]<>'600' AND [Disc Area Id]<>'700' AND [Disc Area Id]<>'701' AND [Disc Area Id]<>'702' AND [Disc Area Id]<>'703' AND [Disc Area Id]<>'704' AND [Disc Area Id]<>'705' AND [Disc Area Id]<>'706' AND [Disc Area Id]<>'800' AND [Disc Area Id]<>'900'

                                  AND [Quality Velocity]='Q'

                              THEN 1

                              ELSE NULL

                          END

                           

                          Which my hope would be that I would get a distinct count on Serial #s using this calculation.  Not just the entire data set.

                          • 10. Re: Some basic questions from a very new user: filters, sheets, calcs
                            Mark Holtz

                            Almost there--remember, Count Distinct wants to be fed unique values, so if you want to count unique Serial Numbers, you need the result of the expression you're "doing count distinct on" to be Serial Number.

                             

                            COUNTD( 

                             

                            IF ISNULL([Dispo Cd])

                                    AND [Prototype Ind]='N'

                                    AND [Ext Cpy Stat]<>'D'

                                    AND ([Fac Prod Fam Cd]='ACOM' OR [Fac Prod Fam Cd]='SCOM' OR [Fac Prod Fam Cd]='LAP' OR [Fac Prod Fam Cd]='RM' OR [Fac Prod Fam Cd]='SCRD')

                                    AND [Cuz Area Id]<>'0_DEFECTS'

                                    AND NOT STARTSWITH([Event Desc],'0')

                                    AND [Disc Area Id]<>'501' AND [Disc Area Id]<>'525' AND [Disc Area Id]<>'600' AND [Disc Area Id]<>'700' AND [Disc Area Id]<>'701' AND [Disc Area Id]<>'702' AND [Disc Area Id]<>'703' AND [Disc Area Id]<>'704' AND [Disc Area Id]<>'705' AND [Disc Area Id]<>'706' AND [Disc Area Id]<>'800' AND [Disc Area Id]<>'900'

                                    AND [Quality Velocity]='Q'

                                THEN [Serial #]

                                ELSE NULL

                            END

                            )

                            • 11. Re: Some basic questions from a very new user: filters, sheets, calcs
                              Stephen Groff

                              Yes, of course!  Makes sense.

                               

                              I'm responsible for bringing every last one of our facility's metrics into Tableau... with no resources outside of the the internet.  I wish you knew what you've done for me here.  It's like finding the final piece to the border of a puzzle that will frame the rest of what I learn in Tableau.  This was a big baby step today, and a question that I couldn't articulate until this morning... albeit, with a heavy dose of frustration.

                               

                              Thanks again,

                               

                              Steve