8 Replies Latest reply: Aug 3, 2017 7:10 AM by Sasidhar Parupudi

# Help with AGGR function (I think!)

Hi

I have a data set that contains duplicates, which I want to dedupe in the chart I created (Qlik Sense). Here's an example of what I have:

Region, Name, Course, Status

EMEA, John, A, Passed

EMEA, Natalie, A, Failed

APAC, Sue, B, Passed

AMER, Frank, C, Passed

EMEA, Jenny, A, Passed

APAC, Dipal, B, Failed

EMEA, John, A, Passed

APAC, Jo, A, Passed

AMER, Frank, C, Passed

And so on... I want to show the number of courses passed by Region and need to exclude the duplicates. So the result I'm looking for is as follows:

EMEA - 2

APAC - 2

AMER - 1

What I have so far is "count (if ([status] = 'passed', [course]))

This correctly counts the number of passed courses (without de-duping) so my results are:

EMEA - 3

APAC - 2

AMER - 2

Please can someone offer me some advice on how I can remove the duplicates? I think I may need to use the AGGR function?

Thanks,

Becs

• ###### Re: Help with AGGR function (I think!)

"count (DISTINCT if ([status] = 'passed', [course]))

• ###### Re: Help with AGGR function (I think!)

Thanks Tim. I can't count distinct course or name because I need to show where multiple people have passed a course or where a person has passed multiple coursed.

Does that make sense?

• ###### Re: Help with AGGR function (I think!)

Hi Rebecca,

maybe

Count (DISTINCT if ([Status] = 'Passed', [Course]&Name))

Regards,

Antonio

• ###### Re: Help with AGGR function (I think!)

Thanks Antonio, that appears to be correct!

I really appreciate your help on this!

• ###### Re: Help with AGGR function (I think!)

I suggest using set analysis instead of using the if statement

Count(DISTINCT {<Status = {'Passed'}>} Name&'|'&Course)