6 Replies Latest reply on Apr 26, 2018 12:09 PM by Stephen Groff

# Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

Hello experts,

I am stuck in a situation that I am not sure can be solved using a calculation

The following calculation is attempting to find the machines we produce that have gone through a final quality check with zero defects.  This calculation works.

IF ([Product Family]='ACOM' OR [Product Family]='SCOM' OR [Product Family]='LAP' OR [Product Family]='RM' OR [Product Family]='SCRD')

AND [EXT_CPY_STAT]<>'D'

AND [Q/V]<>'V'

AND [FA ID]=='400'

AND [CA ID]='0_DEFECTS'

THEN [Serial #]

END

The problem is, I'm missing a handful of machines that also had zero defects that don't follow the above criteria.  Specifically, CA ID is different than '0_DEFECTS'

The only way I know that this wasn't a defect is because a different column says so... DISP='2X'

So one might think that I can just add in "AND [CA ID]='0_DEFECTS' OR [DISP='2X'...  but not all 2X's are non-defects.

In Excel, I got around this by creating a bunch of separate tables... one for machines, one for defects, etc. etc.  So I would just compare the serial # on my Machines table to the serial # defects tables... if it was found on the defects table, then it wasn't a zero defect machine.  But I don't have that luxury here... I have 1 giant table that I would rather use than splitting up the table into a bunch of separate tables... maybe that's the way to go... but I always end up with errors because I'm still very new to Tableau.

Does anyone understand my problem enough to respond?

• ###### 1. Re: Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

Ok

As I understand the issue after all the checks are done

all of the CA ID = '0_DEFECTS'   Machines (that also meet the other criteria) are being correctly identified (i.e. you are getting their serial numbers)

and None of the CA ID = '2x'  machines are being Identified but some meet all the other criteria (not all) and should have been identified - so the question is what else is unique about the ones that should be identified that could be added to isolate them (sorta like ...AND    ( [CA ID]='2x' and [something else] = What )  then serial #

If your answer is that there is nothing else unique you are left with creating a list of machines that are CA ID =2x that would qualify and adding it in you data set

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

This is the answer I was afraid of... there are no other unique identifiers.  You would think in 160 columns of data there would be plenty of identifiers, but sadly... very sadly... there are not.  Thanks for confirming my suspicions.  I appreciate the guidance.

Steve

• ###### 3. Re: Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

That may be true but there is something -

I have worked a lot in forecasting and production planning -

If I understand the issue you have a list of machines

Think about this that fall into this CA ID 2X group ans on a regular basis (lets say week) you get a report of some kind on thier rejections

Is there a way you could take that list and bring it into tableau and join it to your other data on the machine serial number -

then the condition is           if CA ID = 2X and [defects]=0

You could update that data with a "refresh" of the data source

just a thought

Jim

• ###### 4. Re: Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

The problem lies in how we collect our data.

The database I connect to that gives me all of our assembly DEFECT data was built back in the early 90's and for whatever reason we are still using it.  The company refuses to make any additions... or change anything for that matter.  It's setup as a web page, and when we record a DEFECT, we go to this website and input our data.  There are no standards, or processes... which means that the data is incredibly variable and user-defined.  It's a nightmare... hence the 160 columns.

We have SAP, but currently do not support the ability to bring in our data through SAP (at least at my facility) and into Tableau.

With that being said... The database I am connected to and thus, the table I am connected to with the 160 columns of data is a list of DEFECTs.  That's it... That's all it is.  We do not have a list of machines, or anything else.  Just getting our machine count requires an imagination just to create work-arounds... and relying on the people on the assembly floor to put in the user-defined data so I can actually have unique identifiers... Such as 0_DEFECTS (we only input 0_DEFECT because without a defect listed against the machine (assuming the machine is defect-free), it's impossible for me to know that a machine actually went down the assembly line, so we create a dummy defect).

I realize how convoluted this is, but it's all I have to work with now.  Many of our other facilities have been on-boarded into SAP and there data-governance standardize... we have not, nor do we plan to be.  It's a real challenge... I can create the 10 different tables to make things easier on myself like I did in Excel... but as soon as I start creating tables in Tableau... I start running into problems with blending data, aggregations, and joining (relationship) parameters.

I guess by the end of this project (bringing all of our data metrics into Tableau), I'll be a pro.

• ###### 5. Re: Calculation Too Complicated  - No knowledge of how to approach or solve - Ideas?

Have you looked at Tableau Prep - just released this week - bringing 10 tables together with that could make things a lot easier -

I was on the beta team and it has a wide range of data cleansing and combining ability