14 Replies Latest reply: Jan 18, 2017 9:25 PM by Ozzie Boeuf RSS

    What is a suitable Set Analysis expression for distinct counts based on condition/groupings

    Ozzie Boeuf

      Hi,

       

      I'm trying to create a suitable expression using the Qlik Sense KPI extension that returns a distinct count of  Members who appear in both Divisions (there are currently only 2).

       

      An extract of my 5,000 row dataset looks like this:

       

       

      DivisionAreaMember
      Region EastGraftonGeorge
      Region EastNewcastleJack
      Region EastByron BayJoe
      Region WestBullamankaKatrina
      Region WestBroken HillJoe
      Region WestParkesBluey
      Region WestAlburyBeachball
      Region WestWentworthCostello
      Region WestWilcanniaAbbott
      Region WestDubboJack
      Region WestGoulburnAmos
      Region WestCobarJoe

       

      Looking at this dataset, based on my requirements, the expression should return the correct answer of 2 (Jack and Joe - as they at least appear once in both Region East and Region West)

       

      I'm stuck at the moment and started with this expression when maybe I should use set analysis:

       

      aggr (count(Member), Division, Member ) )

       

      Any help will be greatly appreciated.

       

      Thanks.