9 Replies Latest reply on May 21, 2018 5:11 PM by Zhouyi Zhang

    COUNTBLANKS & COUNTROWS - Tableau Equivalents

    Stephen Groff

      Wow, 50 views and no solutions?

       

       

      Greetings Tableau experts... I'm having a hard time writing about a few calculations that I think will require preliminary calcs to do so....

       

      I am trying to figure out a way to count my non-defective products.

       

      I have the basics I think I need...  The number of completed machines, the number of defective machines, and the serials that join each together.

       

      Now I just need to find the number of clean (non-defective) machines produced.  Easily this would mean... (total machines - # of defective) = # of defect-free

       

      However, 1 machine can have an unlimited # of defects.

       

       

      Below is the long version of the problem... see attached file... See open sheet...

       

      In powerpivot I used a calculated column to count the number of defects using a serial # relationship that looked like this:

       

      400 Defects:=CALCULATE(COUNTROWS('All Events'),'All Events'[FA ID]="400")

       

      The follow-up calculation simply counts the blank rows (serial #s that weren't found on my defects table) and adds them up:

       

      All 400 No Defects:=CALCULATE(COUNTBLANK([400 Defects]))

       

      The end result gives me the number of defect-free machines that went through quality gate 400 (FA ID 400)

       

      I would like to figure out how to do this in Tableau, but my research into the issue thus far has yielded no promising results.  Does anyone know how?

       

      Thank you!!!!!

       

      Sheet6 will show you the # of defects, # of Machines completed and the serial # attached.  Not sure what the calculation would look like... something beginning with COUNT(IIF...

       

       

       

      Reattached file.... 7:56am 5/18/2018 CST

        • 1. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
          Zhouyi Zhang

          Hi, Stephen

           

          Is this the result you expected? A little bit confuse of what's your logic to calculate, If you could explain your expected result will be more helpful

           

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
            Stephen Groff

            ZZ,

             

            Your calculation is really close.  But not close enough where I'm sure that these are the correct numbers I need to see.  I implemented your calculation and refreshed my data both in my Excel file (which I know is correct) and the Tableau version of the numbers.  Here's the #'s I got:

             

            Excel Version (correct):

             

            Your Version (with updated filter to [Date]=2018, and added in [Product Family])

             

            I think the numbers are just a little higher because the calculation does not COUNTD (distinct count) the serial #.  I only want to count the serial # once.  However, my [Machines - PDI Complete] calculation accounts for COUNTD so I'm not sure where the problem is.  Ideas?

             

            re-Attached the updated .twbx on the original post.

             

            Thanks ZZ, really appreciate your help on this one.

            • 3. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
              Zhouyi Zhang

              Hi, Stephen

               

              If you have filter, you need add the filter context as shown below. I checked the "RM" product, the calculation should be correct now. For other products, it doesn't match your screenshot of excel, not sure why, will leave it to you to check, or if possible, can you post the excel so that I can compare

               

              ZZ

              2 of 2 people found this helpful
              • 4. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                Stephen Groff

                Whoa whoa whoa.... What in the heck is "Add to Context"???  That changed everything... the numbers aren't exact to Excel... but within 1 or 2... which is the normal because Tableau is live data, Excel is not (demands that I constantly refresh the data)....

                 

                Anyway... I'm going to have to read up on 'Add to Context'... I really didn't know that existed.

                 

                You solved my problem.  Now I have a big ASK of you.

                 

                Could you walk me through your calculation and explain what each part is doing?

                 

                SUM({FIXED [Serial #]:[Machines - PDI Complete]}) - what are you telling Tableau to do here?

                - ?

                SUM(

                {FIXED [Serial #]:SUM(

                IF {FIXED [Serial #]:[Defects - 400 Assy]}>0 THEN 1 ELSE 0 END)}) - what are you telling Tableau to do here?

                 

                I've had a hard time wrapping my head around LODs and I have pretty much stopped trying to understand them...  The word "aggregation" just completely flips my mind inside out... so when people start talking aggregation this and aggregation that... my brain shutdown.

                 

                ZZ, thank you so much for your help.  I really do appreciate your time.

                • 5. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                  Zhouyi Zhang

                  Hi, Stephen

                   

                  Glad it worked. Actually I didn't fully understand what you want to try achieving, but I guess you already figure out the Machines part as well as defects part, so the only thing left is count these two based on serial #. (correct me if I mis-understand.). If this is true, please see my comments below

                   

                  SUM({FIXED [Serial #]:[Machines - PDI Complete]}) - what are you telling Tableau to do here?

                  // ZZ: this line here is try to sum up how many machines complete per serial #, that will give you the total machines of completing

                  - ?

                  SUM(

                  {FIXED [Serial #]:SUM(

                  IF {FIXED [Serial #]:[Defects - 400 Assy]}>0 THEN 1 ELSE 0 END)}) - what are you telling Tableau to do here?

                  //ZZ: this line is similar as above, but just count those with defect >0 which means there is at least one defect, however, there might be more than 1 defect for each serial #, so introducing  the if statement to flag if any serial # with defect # > 0 then count as 1 serial #.  and them, sum up you get total # of serial which has defect.

                   

                  The last part is total machines - total defects

                   

                  As this uses Fixed LOD, which is pre-calculate before any filter applies, you need add it to context so that it can be involved in the FIXED calculation

                   

                  ZZ

                  1 of 1 people found this helpful
                  • 6. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                    Stephen Groff

                    Thank you!  As always ZZ, your help is very much appreciated.  You made my day much easier.... and my weekend

                    • 7. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                      Zhouyi Zhang

                      you are welcome, and hope you enjoyed

                       

                      ZZ

                      • 8. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                        Stephen Groff

                        ZZ I need help!

                         

                        After looking into the calculation I found out it's wrong.

                         

                        I've worked my way through as best I could but I cannot come up with the right number.

                         

                        If you separate out the actual LODs you put together one = 940 (machines) but it's the second LOD that is somehow, some way... not computing correctly.

                         

                        If am I reading your original LODs correctly:

                         

                        SUM({FIXED [Serial #]:[Machines - PDI Complete]}) - THIS ONE GIVES ME MY TOTAL NUMBER OF MACHINES - (same as [Machines - PDI Complete] so this may be unnecessary....??

                        -

                        SUM(

                        {FIXED [Serial #]:SUM(

                        IF {FIXED [Serial #]:[Defects - Q400 Assy]}>0 THEN 1 ELSE 0 END)}) - THIS ONE IS NOT TOTALING MY TOTAL DEFECTS FOR Q400 ASSY, IT'S JUST COUNTING THE TOTAL NUMBER OF MACHINES WITH DEFECTS

                         

                        I've tried it 25 different ways...  I've highlighted the part in the calculation that is faulty... I need to SUM all the defects tied to those serial numbers... not count total serial numbers that have more than 1 defect.  I tried... I failed... I'm freaking out.

                        • 9. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents
                          Zhouyi Zhang

                          Hi, Stephen

                           

                          Sorry to hear that. Can you show your issue with your sample data and the expected result?

                           

                          ZZ