3 Replies Latest reply on Aug 5, 2013 10:08 PM by ruth.wyer

    counting distinct values, including nulls, under multiple conditions

    ruth.wyer

      Hi,

       

      My apologies in advance for the long email and that I cannot upload a workbook as it contains sensitive data.  I am using v8 connecting directly to 7 joined tables so I am going to try and simplify the fields I'm referencing for this question:

      Exception_ID (4 digit unique identifier)

      Staff_Name (text)

      Account_No

      Dept

       

      I have a requirement to count the number of times an exception occurs against a staff name and display on a dashboard.  I need to count nulls as this shows as a green tick.  Problem is that the data I am working with comes straight from a database which has some challenges, one of which is that there are sometimes erroneous duplicate exception_IDs.

       

      e.g.

      1234 | Joe Bloggs | 9999 | Sales

      1234 | Joe Bloggs | 9999 | Sales

       

      To get around this I am using zn(lookup(countd(exception_ID),0))

       

      This is all working beautifully but now I have a requirement to exclude certain exceptions based on dept.  Where there are 2 exceptions with different exception_IDs and different depts but the same account no, I need to exclude one.

       

      e.g. in the example below, I would only want to count 1 (doesn't matter which)

      1234 | Joe Bloggs | 9999 | Sales

      1685 | Joe Bloggs | 9999 | Service

       

      but in the example below, I would want to count both because the depts are the same

      e.g.

      1234 | Joe Bloggs | 9999 | Sales

      1972 | Joe Bloggs | 9999 | Sales

       

      Can anyone help me expand zn(lookup(countd(exception_ID),0)) accommodate this new requirement?  Thank you in advance