How to get look to return counts of 0

Answered

Comments

3 comments

  • Official comment
    Avatar
    David Sallusti

    Hi Jan,

    Great example of Merging results. Here is how to do it

    1. Create a report with the "Client Full Name" ONLY

    2. Click on gear icon and select Merge Results

    Use the Visit Explore and add: 1. Client Full Name and Visit Distinct Count

    Click Run (As you can see, it will show you all clients with visits but what about the ones without?

    Click Save and then Run the report:

    1. As you can see in picture below, the CLIENT Explore fetches ALL CLIENTS, However, the Visit Explore will only show you the clients with visits.

    2. SINCE you are merging results FROM CLIENTS explore which shows you all Clients, the Merge will match the Client Full Name (Key ID) to the Client Full Name in the Visit Explore (Merged Explore)

    3. IF the Merge doesnt identify the Key value of CLIENT (In this case, the Client Full Name), it will display 0 value as you can see below.

     

    Hope this helps!

     

    Comment actions Permalink
  • Avatar
    Jan Barton

    This worked nicely to pull in the 0 values (and follows vlookup logic which I use a lot).

    Follow up questions.

    1. Should we use unique fields as the Key ID (client id vs. client full name) to avoid any mis-matching if clients were to have identical names?

    2. How do I add more fields to the data (employee name, service code, visit date range)?

    3. How do I add filters to the data (i.e. to show only the clients with 0 counts in the past 28 days)?

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    David Sallusti

    Hi Jan Barton

    1. Unique ID must match. For instance, you can use Client ID on both reports or Client full Name on both reports.

    2. You can add them to the second report within the services explore directly and THEN save the merge.

    3. Same as above, filters etc are handled before the merge, once you save it it will show the results filtered. If you need to go back and change the filters then you can click on the gear icon beside the explore name on the left side and click Edit

    0
    Comment actions Permalink

Please sign in to leave a comment.