
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)1 of 1 people found this helpfulZZ,
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)1 of 1 people found this helpfulWhoa 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)1 of 1 people found this helpfulyou 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)1 of 1 people found this helpfulHi, Stephen
Sorry to hear that. Can you show your issue with your sample data and the expected result?
ZZ

10. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 29, 2018 6:16 AM (in response to Zhouyi Zhang)1 of 1 people found this helpfulZZ,
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:
your calc:
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"
Any help you could provide would be greatly appreciated. Thanks ZZ!

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

12. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 29, 2018 6:27 AM (in response to Zhouyi Zhang)1 of 1 people found this helpfuladded the INT instead of COUNTD. Good catch. Thank you!

13. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Zhouyi Zhang May 29, 2018 6:31 AM (in response to Stephen Groff)No worries, glad to help
ZZ

14. Re: COUNTBLANKS & COUNTROWS  Tableau Equivalents
Stephen Groff May 31, 2018 10:59 AM (in response to Zhouyi Zhang)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.
You wouldn't happen to know anything about this error would you? See attached

Oracle Error.txt.zip 1.4 KB
