3 Replies Latest reply: Jul 5, 2016 5:25 AM by Bart-Jan Prent RSS

    Multiple dimension values in two cell. How to count properly?

    Bart-Jan Prent

      Hi there,

       

      I have a dataset like the table below (larger though):

      IDAppBusiness functionName
      231BroadDisplayKok; Bok; TokPuk; Put
      245SmallDisplayStone; Bone; HomeStuck; Cut; Duck

       

      Now I made subfields for 'Business function' and 'Name' like this:

                          SubField("Business function", ';') as Tags,

                          SubField("Name", ';') as Tags2,

       

      The following problem now occurs when I want to count the Business function or Name:

      IDAppTags (Business function)Tags2 (Name)
      231BroadDisplayKokPuk
      231BroadDisplayBokPuk
      231BroadDisplayTokPuk
      231BroadDisplayKokPut
      231BroadDisplayBokPut
      231BroadDisplayTokPut
      245SmallDisplayStoneStuck
      245SmallDisplayBoneStuck
      245SmallDisplayHomeStuck
      245SmallDisplayStoneCut
      245SmallDisplayBoneCut
      245SmallDisplayHomeCut
      245SmallDisplayStoneDuck
      245SmallDisplayBoneDuck
      245SmallDisplayHome

      Duck

       

       

      This would mean that even with the set analysis below my business function Tok, Kok, Bok is still counted twice. And Stone, Bone, Home is counted tree times if I am correct:

       

      count(aggr(sum(DISTINCT [Tags]), [ID]))

      OR

      count(aggr(sum(DISTINCT [Tags]), [Tags2]))

       

      I somehow want to combine the DISTINCT ID and Tags2 but I don't know if that is possible. Does anyone have a clue what I am talking about and would show me the solution (A)?

       

      Would be great help if anyone knows how!!!

       

      Tnx