Create a Count Distinct when looking at multiple columns

Reply
Highlighted
Yellow Belt

Create a Count Distinct when looking at multiple columns

I have a data set that is masked to a set of Suppliers.  I am trying to show them for each unique Job Posting ID # how many Job Seekers are set to interview for the position (we deal in temp labor).  I have 2 interview dates that I can pull in for each job seeker, so I'm trying to do something like this:

 

case when (`Job Seeker First Interview Date`,`Job Seeker Last Interview Date`)>0 then count(distinct(`Job Seeker`) else 0 end

 

But the beast mode isn't functional, thoughts on a better way to show what I am trying to get too?

 

Thanks Dojo!


Accepted Solutions
Yellow Belt

Re: Create a Count Distinct when looking at multiple columns

Okay. If you could make an assumption such as "There will never be a last interview date without a first interview date" you could eliminate some of this case. But if there are situations when they would have a last and not have a first then you will need this whole expression.

 

Here is a crack at it:

 

count(distinct nullif((case when `Job Seeker First Interview Date` is not null

then `Job Seeker`

when `Job Seeker Last Interview Date` is not null

then `Job Seeker`

else 0

end) ,0))

 

I cant remember if Domo plays nicely with numbers being used in text fields. If not, you may need to swap out the zeros there for ' ' to get it to work.

 

 

 

Alternatively, this could/should work so maybe try this first:

 

count(distinct (case when `Job Seeker First Interview Date` is not null

then `Job Seeker`

when `Job Seeker Last Interview Date` is not null

then `Job Seeker`

else null

end))

 

But Domo isn't great at using nulls in cases like that IIRC so that may blow up in which case try the case ablove.

 

Good luck!


All Replies
Yellow Belt

Re: Create a Count Distinct when looking at multiple columns

Hi Jenn,

 

I think the problem here may be the first part of that case. What are you trying to do with:

(`Job Seeker First Interview Date`,`Job Seeker Last Interview Date`)>0

 

There is no function there as is. Are you trying to calculate the presence of one or the other? What is the logical statement you are trying to achieve?

 

 

 

Yellow Belt

Re: Create a Count Distinct when looking at multiple columns

Yes - if either of these have data in it, then I want it to count each distinct job seeker who has something in there if that makes more sense.

Yellow Belt

Re: Create a Count Distinct when looking at multiple columns

Okay. If you could make an assumption such as "There will never be a last interview date without a first interview date" you could eliminate some of this case. But if there are situations when they would have a last and not have a first then you will need this whole expression.

 

Here is a crack at it:

 

count(distinct nullif((case when `Job Seeker First Interview Date` is not null

then `Job Seeker`

when `Job Seeker Last Interview Date` is not null

then `Job Seeker`

else 0

end) ,0))

 

I cant remember if Domo plays nicely with numbers being used in text fields. If not, you may need to swap out the zeros there for ' ' to get it to work.

 

 

 

Alternatively, this could/should work so maybe try this first:

 

count(distinct (case when `Job Seeker First Interview Date` is not null

then `Job Seeker`

when `Job Seeker Last Interview Date` is not null

then `Job Seeker`

else null

end))

 

But Domo isn't great at using nulls in cases like that IIRC so that may blow up in which case try the case ablove.

 

Good luck!

Purple Belt

Re: Create a Count Distinct when looking at multiple columns

Hey Jenn!

 

I wasn't sure what the first bit of your case was supposed to be doing, I assumed that it was taking the difference between the two dates, so I built this case statement:

 

Count(distinct(case when DATEDIFF(`Job Seeker Last Interview Date`, `Job Seeker First Interview Date`) > 0 then `Job Seeker` else 0 end))

 

The reason I switched the position of the last interview date and the first interview data in the DATEDIFF is because if the first date is before the second date, the DATEDIFF will return a negative value, and your case statement will not work.

 

One thing I would also do is use the beast mode to filter out zeroes, because a zero value will be counted as a distinct value. 

 

I hope I'm understanding what you're trying to accomplish. Is there a reason that you can't just count distinct job seekers by position without doing anything with the dates? 

 

Let me know if this works for you!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Yellow Belt

Re: Create a Count Distinct when looking at multiple columns

The first response worked once I changed the 0 to '' I'm all set!

 

Thanks!

Announcements
New Dojo experience is here! Here are some quick tips, we would also love your feedback. Click here!