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

    Logical solution required

    Rajesh L M

      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
          Shinichiro Murakami

          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
            Rajesh L M

            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:

              

            projectwbsactivitystatuscodewbs ValidityReason for Invalidity
            project 1wbs1act1completedeq.02Valid
            project 1wbs1act2completedeq.02Valid
            project 1wbs1act3completedeq.08Valid
            project 1wbs1act4completedeq.08Valid
            project 1wbs2act5completedeq.02InValidDouble eq.08 present
            project 1wbs2act6completedeq.08InValidDouble eq.08 present
            project 1wbs2act7completedeq.08InValidDouble eq.08 present
            project 1wbs2act18not startedeq.09InValidDouble eq.08 present
            project 1wbs3act8not startedeq.02InValideq.08 is missing
            project 1wbs3act9completedeq.09InValideq.08 is missing
            project 1wbs4act10not startedeq.08InValideq.02 is missing
            project 1wbs4act11completedeq.08InValideq.02 is missing
            project 1wbs5act12completedeq.08Valid
            project 1wbs5act13completedeq.10Valid
            project 2wbs6act14completedeq.08Valid
            project 2wbs6act15In Progresseq.02Valid
            project 2wbs7act16completedeq.08InValideq.02 is missing
            project 2wbs7act17not startedeq.08InValideq.02 is missing
            project 2wbs7act19completedeq.09InValideq.02 is missing
            project 2wbs8act20not startedeq.02InValideq.08 is missing
            • 3. Re: Logical solution required
              Ankit Bansal

              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
                nazim zmirli

                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
                  Rajesh L M

                  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
                    Ankit Bansal

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

                     

                     

                    Hope this helps.

                    • 7. Re: Logical solution required
                      Rajesh L M

                      Hi Ankit,

                       

                      Appreciate you help.

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

                       

                      Thanks in adavnace