4 Replies Latest reply on Jun 8, 2016 2:26 AM by Yvonne Cheng

    How to check Missing Item

    Yvonne Cheng

      Hi All, In my company, each employee has a set of required certificates. I would like to have one report to show the employee who do not hold certain certificates. The logic is If the employee does not have ECDISTRANSAS OR ECDISJRC, then the employee id will be showed in the report. Meanwhile the report will show which certificate he/she dun have.

      I tried several logic functions, but they dun work. Hope you can help me. Thank you.

      Pls see attached sample workbook.

        • 1. Re: How to check Missing Item
          Charlie Archer

          Hi Yvonne,

           

          I think i answered the above question by using two calculated fields and an additional filter on the view:

           

          I filtered the view to only include the ECDISTRANSAS and ECDISJRC license codes.

           

          [Certified flag] - used as a filter to only show the employees that didn't have both of the ECDISTRANSAS and ECDISJRC certificates:

           

          SUM({FIXED [Employee ID]: SUM(IF [License Code] = 'ECDISTRANSAS'

          OR [License Code] = 'ECDISJRC'

          THEN 1 ELSE 0

          END)}) < 2

           

          This formula uses an if statement fixed to the employee ID level of aggregation to give a value of 1 for each of the two certificates the employee holds. Where they have both the certificates the value is two, if they are missing one they have a value of 1 or 0. The filter can therefore be used to include only employees with a value of less than 2.

           

          [Missing certification] - used to show the certificate that employees are missing:

           

          IF [License Code] != 'ECDISTRANSAS' THEN 'ECDISTRANSAS'

          ELSEIF [License Code] != 'ECDISJRC' THEN 'ECDISJRC'

          ELSE null

          END

           

          If the license code for the employee within the view is not ECDISTRANSAS then take this certificate as missing, and the same for ECDISJRC.

           

          Hope this solves your issue.


          Thanks,
          Charlie

          • 2. Re: How to check Missing Item
            Yvonne Cheng

            Dear Charlie,

            Thank you so much. It looks great.

            I will try and revert with the result.

            Best Regards

            Yvonne

            1 of 1 people found this helpful
            • 3. Re: How to check Missing Item
              Yvonne Cheng

              Dear Charlie,

              The calculation you provided is helpful. Thank you

              Best Regards

              Yvonne

              • 4. Re: How to check Missing Item
                Yvonne Cheng

                Dear Charlie,

                I got one issue here, hoping you could help me.

                With the second calculation to show the missing certificate, it encounters problems.

                For example, UAAL00020 holds neither ECDISJRC nor ECDISTRANSA, but the final view shows only ECDISTRANSA is missing. @@@@

                UAAL00022 holds ECDISTRANSA but no ECDISJRC, but the final view shows both certificates are missing.

                Pls see below screenshots.

                Appreciate you could give me the solution to fix the errors. Thank you very much.

                Charlie Archer