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

    Help with AGGR function (I think!)

    Rebecca Parkinson

      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