Help with AGGR function (I think!)

[#1]

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

[zhadrakas]

what about

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

[#3]

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?

[antoniotiman]

Hi Rebecca,

maybe

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

Regards,

Antonio

[antoniotiman]

[#6]

Thanks Antonio, that appears to be correct!

I really appreciate your help on this!

[sunny_talwar]

I suggest using set analysis instead of using the if statement

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

[Anil_Babu_Samineni]

Please close this thread by flag correct answer and may be helpful if reach your requirement

[sasiparupudi1]

Please try

Count(distinct {<Status={'Passed'}>}Name)

hth

Sasi