Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
what about
"count (DISTINCT if ([status] = 'passed', [course]))
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?
Hi Rebecca,
maybe
Count (DISTINCT if ([Status] = 'Passed', [Course]&Name))
Regards,
Antonio
Thanks Antonio, that appears to be correct!
I really appreciate your help on this!
I suggest using set analysis instead of using the if statement
Count(DISTINCT {<Status = {'Passed'}>} Name&'|'&Course)
Please close this thread by flag correct answer and may be helpful if reach your requirement
Please try
Count(distinct {<Status={'Passed'}>}Name)
hth
Sasi