
1. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Zhouyi Zhang May 18, 2018 5:19 AM (in response to Stephen Groff)1 of 1 people found this helpfulHi, 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

2. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 18, 2018 5:55 AM (in response to Zhouyi Zhang)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?
reAttached the updated .twbx on the original post.
Thanks ZZ, really appreciate your help on this one.

3. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Zhouyi Zhang May 18, 2018 6:13 AM (in response to Stephen Groff)2 of 2 people found this helpfulHi, 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

4. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 18, 2018 6:26 AM (in response to Zhouyi Zhang)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 May 18, 2018 6:45 AM (in response to Stephen Groff)1 of 1 people found this helpfulHi, 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 misunderstand.). 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 precalculate before any filter applies, you need add it to context so that it can be involved in the FIXED calculation
ZZ

6. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 18, 2018 11:23 AM (in response to Zhouyi Zhang)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 May 20, 2018 6:42 PM (in response to Stephen Groff)you are welcome, and hope you enjoyed
ZZ

8. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 21, 2018 1:29 PM (in response to Zhouyi Zhang)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 May 21, 2018 5:11 PM (in response to Stephen Groff)Hi, Stephen
Sorry to hear that. Can you show your issue with your sample data and the expected result?
ZZ