8 Replies Latest reply on Feb 25, 2016 8:00 AM by Ben Page

# LOD (I think) and a 'tough' logic problem...well for me anyway

Gurus...

How do I count the shops which failed all their inspections

Shop
Inspection Result
afail
afail
apass
bpass
cfail
cfail
dpass
dpass
epass
efail
efail
efail
efail
• ###### 1. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

Hey Timothy,

IF ({FIXED [Shop] : SUM(IF [Inspection Result] = 'fail' THEN 1 END)} = {FIXED [Shop] : COUNT([Shop])}) THEN 1

ELSE 0

END

• ###### 2. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

BTW

The answer cant involve 'maths'... i.e. cant convert Pass=1 and Fail=0

• ###### 3. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

hi Timothy,

So this a little complicated, but one you understand the method it can be used for many situations.

btw in my solution I've broken the calculated fields up for explanatory processes, but in your final solution could nest them all in to one.

So the first thing is to get the total number of inspections per shop, using fixed LOD

[Number of Inspections]

{FIXED [Shop]: SUM([Number of Records])}

Next we get the number of fails per shop. Here we use an LoD, but embed a (row level) iif statement into the formula so it only sums up fails

[Number of Inspections Failed]

{FIXED [Shop]: SUM(IIF([Inspection Result]='fail',[Number of Records],0))}

Now we can use these 2 LoDs to determine if a Store has failed all it's inspections, and then do a countd on those stores

[Count of All Failed]

COUNTD(IIF([Number of Inspections Failed]=[Number of Inspections],[Shop],NULL))

As you can see we've used the same trick of embedding a row level iif statement before the aggregation.

I've done an explanation of what's going on with this Row Level (in formula) Filter followed by an aggregation here

Re: How to create a Scatter Plot based on same calculation but different dimensions

Hope this does the trick, and more importantly makes sense, but if not please post back and I can go into a bit more detail

• ###### 4. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

Thanks so much.. I'll try to get this incorporated in the worksheet

• ###### 5. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

My approach is fairly similar to Derrick's:

Basically counting the shops that have 0 "Pass" results.

Ben

• ###### 6. Re: LOD (I think) and a 'tough' logic problem...well for me anyway

Yep - basically count the fails, count the total. Do they match? If so, all fails.