Max a count

[#1]

Hey all,

So I am trying see whether people have completed a certain course on our LMS system, and came up with this:

= 'Total Complete' & chr(10) & Count({$<Course_Name={'Compliance and Ethics Training', '2015 Annual Compliance and Ethics Training'}>}  DISTINCT PersonID)

The question is, I have another column named "Course_Status" and am trying to get a count if they have completed it (complete or not complete). They only need to complete one or the other, so I am trying to get a max, but it seems to be adding both of them, throwing off my count.

I don't have much experience in coding, so I have been browsing a lot.. a lot of forums.

Any advice?

[arulsettu · Accepted solution]

may be this

'Total Complete' & chr(10) & Count({$<Course_Name={'Compliance and Ethics Training', '2015 Annual Compliance and Ethics Training'},Course_Status={'complete'}>}  DISTINCT PersonID)

[sunny_talwar]

How about this:

='Total Complete' & chr(10) &

Count({$<PersonID = p({<Course_Name={'Compliance and Ethics Training'}, Course_Status = {'Complete'}>}) + p({<Course_Name={'2015 Annual Compliance and Ethics Training'}, Course_Status = {'Complete'}>})>} DISTINCT PersonID)

[swuehl]

Maybe using a union

'Total Complete' & chr(10) & Count({$<Course_Name={'2015 Annual Compliance and Ethics Training'},Course_Status={'complete'}>+<Course_Name={'Compliance and Ethics Training'},Course_Status={'complete'}>}  DISTINCT PersonID)

[ramoncova06]

can you add the max expression you are trying to use ?

[trdandamudi]

May be like below:

= 'Total Complete' & chr(10) & Count({$<Course_Name={'Compliance and Ethics Training', '2015 Annual Compliance and Ethics Training'},Course_Status={'completed'}+Course_Status={'complete'}>}  DISTINCT PersonID)

[#7]

That was perfect, I didn't realize it was as simple as that. Thank you for saving me 3 hours a day!

[Anonymous]

IN COUNT POINT OF YOU IT IS CORRECT AND  IN THE MAX COUNT  POINT OF VIEW TO ADD MAX FUNCTION TO GET THE MAXIMUM COUNT