1 2 Previous Next 18 Replies Latest reply on Jun 8, 2018 5:59 AM by Zhouyi Zhang

# COUNTBLANKS & COUNTROWS - Tableau Equivalents

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

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

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.

1 of 1 people found this helpful
• ###### 3. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

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

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.

1 of 1 people found this helpful
• ###### 5. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

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

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

you are welcome, and hope you enjoyed

ZZ

1 of 1 people found this helpful
• ###### 8. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

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.

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

Hi, Stephen

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

ZZ

1 of 1 people found this helpful
• ###### 10. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

ZZ,

So I've spent a day or two trying to figure this out and it works...  So the problem was on my end... and was not your calculation.

However, I've run into a problem in a new workbook I created using your calculation.  I thought I created it in the same exact way, but I'm running into an aggregation problem.

Is the machine a PDI or PDA machine?:

IF CONTAINS([FA Desc],'PDI') THEN 1

ELSEIF [FA ID]='500' THEN 2 ELSE NULL

END

PDI Machine Count Calc:  IIF([Machines]=1,[Serial #],NULL)

PDA Machine Count Calc:  IIF([Machines]=2,[Serial #],NULL)

However, when inputting your calculation to return how many machines that were completed with zero defects I receive an error:

SUM({FIXED [Serial #]:[Machines - PDI Completed]})

-

SUM(

{FIXED [Serial #]:SUM(

IF {FIXED [Serial #]:[Defects - 300]}>0 THEN 1 ELSE 0 END)})

The bold portion above gives error "result of a level of detail expression must be an aggregate"

1 of 1 people found this helpful
• ###### 11. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

Hi, Stephen

Your original workbook shows the calculation for [Machines - PDI complted] is COUNTD(IIF([Machines]=1,[Serial #],NULL))

While you change it without CountD(). so fix the issue, you have to either use countd as your original workbook shows or in my calculation, add countd()

SUM({FIXED [Serial #]:Countd([Machines - PDI Completed])})

-

SUM(

{FIXED [Serial #]:SUM(

IF {FIXED [Serial #]:[Defects - 300]}>0 THEN 1 ELSE 0 END)})

ZZ

1 of 1 people found this helpful
• ###### 12. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

1 of 1 people found this helpful
• ###### 13. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

ZZ

• ###### 14. Re: COUNTBLANKS & COUNTROWS - Tableau Equivalents

zz,

Quick (probably not quick) question....

I kept your calculation above the same... but added in a custom SQL query... and now when I use your calc it throws a SQL error at me.