7 Replies Latest reply on Sep 17, 2018 9:30 AM by Rajesh L M

# Logical solution required

Hi All,

attached is the workbook which has raw data. if you see the raw data/Detail chart required, you would understand the data/output required.

requirement:

to find wbs% for each project

for each wbs need to find either an equ02 or and equ08

equ02 without equ08 => equ08 missing

equ08 without equ02 => equ02 missing

several equ08 => several equ08

several equ02 => several equ02

wbs is valid only:

if all activities are completed for each wbs(irrespective of multiple equ02 and equ08)

if both equ02 and equ08 present once.

wbs is invalid when:

if equ02 or equ08 is missing then invalid

if more then 1 equ02 or equ08 present then invalid

in the current example:

project1:

wbs % = valid wbs(count)/total wbs(count) =2/5=40%

project2: 2/3=33%

chart one: project wise bar chart: showing project and wbs%

chart two: text chart. showing in detail(invalid data only)

clicking on chart one should show only invlaid wbs like below

• ###### 1. Re: Logical solution required

Hi Rajesh

According to your logic and data, nothing can be valid?

Let me know where I misunderstood.

Thanks,

Shin

• ###### 2. Re: Logical solution required

Hi Shinichiro,

Thanks for the help.

yes you have bit misunderstood in logic:

wbs1 is valid because all activities have status completed for that wbs , similarly wbs 5

wbs 6 is valid because it has both eq02 and eq08 only once

wbs is valid only:

if all activities are completed under that wbs irrespective of multiple equ02 and equ08 / missing equ02 or equ08 (ex: wbs1 and wbs5)

if both equ02 and equ08 present once for that wbs irrespective of any status (ex: wbs6)

output(detail sheet) should look like this:

 project wbs activity status code wbs Validity Reason for Invalidity project 1 wbs1 act1 completed eq.02 Valid project 1 wbs1 act2 completed eq.02 Valid project 1 wbs1 act3 completed eq.08 Valid project 1 wbs1 act4 completed eq.08 Valid project 1 wbs2 act5 completed eq.02 InValid Double eq.08 present project 1 wbs2 act6 completed eq.08 InValid Double eq.08 present project 1 wbs2 act7 completed eq.08 InValid Double eq.08 present project 1 wbs2 act18 not started eq.09 InValid Double eq.08 present project 1 wbs3 act8 not started eq.02 InValid eq.08 is missing project 1 wbs3 act9 completed eq.09 InValid eq.08 is missing project 1 wbs4 act10 not started eq.08 InValid eq.02 is missing project 1 wbs4 act11 completed eq.08 InValid eq.02 is missing project 1 wbs5 act12 completed eq.08 Valid project 1 wbs5 act13 completed eq.10 Valid project 2 wbs6 act14 completed eq.08 Valid project 2 wbs6 act15 In Progress eq.02 Valid project 2 wbs7 act16 completed eq.08 InValid eq.02 is missing project 2 wbs7 act17 not started eq.08 InValid eq.02 is missing project 2 wbs7 act19 completed eq.09 InValid eq.02 is missing project 2 wbs8 act20 not started eq.02 InValid eq.08 is missing
• ###### 3. Re: Logical solution required

Rajesh,

You can write logic something like below:

IF  {fixed [Project],[Wbs] : count(if [Code]='eq.02' then [Code] end )} <= 1

and {fixed [Project],[Wbs] : count(if [Code]='eq.08' then [Code] end )}<=1

then 'Valid'

ELSEIF  {fixed [Project],[Wbs] : countd(if ([Code]='eq.02' or [Code]='eq.08') then [Status] end )} = 1

and  {fixed [Project],[Wbs] : Max(if ([Code]='eq.02' or [Code]='eq.08') then [Status] end )} = 'Completed'

then 'Valid'

ELSE 'Invalid'

end

In your example I am not sure why wbs2 is invalid, All the activities for eq02 and eq08 are compelted still u are saying invalid.

but you can tweak the conditions as per your requirement,  hope you got some idea to write it.

Thanks,

Ankit Bansal

• ###### 4. Re: Logical solution required

For most difficulty levels, anything below "evil" or "extremely difficult", it doesn't pay to guess. You can deduce it all more easily than you can backtrack bad guesses. You do have to potentially find it false and backtrack - if you just write "5" in a likely square with no way to take it back, you're likely to get a failed puzzle.

• ###### 5. Re: Logical solution required

Hi Ankit

wbs2 is invalid because eq.09 is not started and eq.08 exists twice.

Can you share the solution via twbx file?

Thanks

• ###### 6. Re: Logical solution required

Ok got it, I have modified the formula a bit. Attached is the twbx.

Hope this helps.

• ###### 7. Re: Logical solution required

Hi Ankit,

Appreciate you help.

Is it possible to have Reason for Invalidity Column as well?