1 2 Previous Next 15 Replies Latest reply on Aug 21, 2018 9:32 AM by Stephen Groff

# In need of an LOD, but unsure how to create

Hi all,

I have a [Date]

I have a list of machines [SERIAL No] for the entire year. And a count of the total machines produced [Machine Count]

I have a list of Defects [Event No] - All of which have a [Serial No] tied to it

I have a list of [Work Center]  - All of which are tied to the defect, which in turn has a serial # tied to it.

So in the same row (in a table) I have the [Date], [Event No], [Serial No] and [Work Center]

Question:  Of the total number of machines I produced today, which of them were free of Assembly Defects ([Work Center]="Assembly").

I need this to come out as a solid number.... drag and drop... and poof!  There's my number of Machines Free of Assembly Defects for that day.

I've attempted this on a number of occasions, but I'm just not knowledgeable enough with LODs.  Please help!

• ###### 1. Re: In need of an LOD, but unsure how to create

{FIXED Date:COUNTD(If [Work Center]="Assembly" THEN Serial NUMBER END)}

• ###### 2. Re: In need of an LOD, but unsure how to create

Hi,

If you put date in LOD your numbers will be distributed between days.

If you want total figure please use the following:

{COUNTD(If [Work Center]="Assembly" THEN Serial NUMBER END)}

Trust this helps.

D

• ###### 3. Re: In need of an LOD, but unsure how to create

Okay, so I've got a number.

I've used the date 8/1/2018 and the calculation works well.  But it is counting the total number of machines that HAVE Assembly Defects.  It isn't counting the number of Machines that didn't have Assembly Defects

The Assembly Group manager wants to know... of the machines we produced yesterday (or 8/1/2018), which ones didn't count against Assembly?

Considering it is totaling the number of machines with Assembly Defects I'm assuming I could just make another calculation:

{FIXED [Date]: COUNTD (IF [Work Center]<>"Assembly" THEN [Serial No] End )}

???

I don't know why I can't wrap my head around this.

2 of 2 people found this helpful
• ###### 4. Re: In need of an LOD, but unsure how to create

Yes That Will Work. It Will Count Only Those with No Assembly Defects.

1 of 1 people found this helpful
• ###### 5. Re: In need of an LOD, but unsure how to create

Okay!  We are almost there.

I did a test on August 1, 2018.  I used the calculation above but added a filter to narrow down the results:

{FIXED [Date]:COUNTD(IF [Work Center]<>"Assembly" AND [Found Area]="300" THEN [Serial No] END)}

My Final number for that day, according to the calculation was 5 machines that didn't have an Assembly Defect.  But as you'll see below in my raw data, this number is incorrect:

The YELLOW serial numbers are the 5 serial #s the calculation is counting. However, you'll notice that my number should be 3.

I've outlined above in RED the serial numbers that shouldn't have been counted.

The problem here is that a serial number can be duplicated in my list of defects.  That's because we can have more than 1 defect per machine.  The [FOUND AREA] ="300", is a quality check area, so it is possible to find hundreds of defects per machine serial #, the [Work Center] just designates which group is responsible for the defect.

This is always where I get stuck in all my LODs (at least with this Data Model), I have no idea to root out the duplicates.

So... where do we go from here?

• ###### 6. Re: In need of an LOD, but unsure how to create

Anyone?

• ###### 7. Re: In need of an LOD, but unsure how to create

Hi Stephen,

Can you pass on Those lines in your Screenshot as excel data, I am trying, but It is difficult to help and test  using Screenshot.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 8. Re: In need of an LOD, but unsure how to create

Since this is fixed to Date that's why it doesn't understand the serial number duplication. Try the below formula

{FIXED [Date],[Event #]:COUNTD(IF [Work Center]<>"Assembly" AND [Found Area]="300" THEN [Serial No] END)}

1 of 1 people found this helpful
• ###### 9. Re: In need of an LOD, but unsure how to create

See attached excel version of the screenshot

• ###### 10. Re: In need of an LOD, but unsure how to create

Hi Anuvir... your calculation still gave me a count of 5

1 of 1 people found this helpful
• ###### 11. Re: In need of an LOD, but unsure how to create

Well Stephen,

Looks Like You were in Some Hurry.

Here It is, I get 3 , but to keep it Distinct Count You need to keep out Serial Number. Logic is you first have to Exclude those which are having Assembly plus others and then countD with in left overs.

Thanks

Deepak

So If you find this helpful, you may mark it as Helpful and CORRECT and not Assumed Answered

1 of 1 people found this helpful
• ###### 12. Re: In need of an LOD, but unsure how to create

This is pretty good Deepak. Just another take on this inspired by your idea

Section A - Flags all the Serial # with Work center as assembly

Section B- Flags only those Serial # without any Work center as assembly

Section C- Counts the Serial #

1 of 1 people found this helpful
• ###### 13. Re: In need of an LOD, but unsure how to create

Yes Good Work..This was not easy to Crack

1 of 1 people found this helpful
• ###### 14. Re: In need of an LOD, but unsure how to create

Thank you guys!  I am grateful for your time and energy in trying to solve this.  I've tested this solution and it works great.

I'll have another brain buster that I've been chipping away at for 4 weeks that I just can't figure out the logic for...  I hope to employ both of your expertise on the matter.  I will post later today.

Thank you both very much, it is appreciated.

1 2 Previous Next