6 Replies Latest reply on Oct 18, 2016 6:30 AM by xinyi.ng

    Counting distinct by category

    xinyi.ng

      Hi

       

      I have this following set of data:

       

        

      KeyCategoryCheck_Code
      1A1
      1B2
      2C1
      2C1
      3A1
      3B2
      3C3

       

      My aim is to create a bar chart to list the count of distinct key, by category.

      The complication comes in because for every unique key with different categories, it shld fall into just 1 category. Do determine which category it falls under (based on the lowest check_code for every unique key). So I should get something like this:

         

      KeyCategoryCheck_CodeFinal Category
      1A1A
      1B2A
      2C1C
      2C1C
      3A1A
      3B2A
      3C3A

       

      Eventually I hope to get a bar chart with this breakdown:

        

      CategoryCount(Distinct(Key))
      A1
      B0
      C1

       

      Appreciate if someone could assist please, thanks!

        • 1. Re: Counting distinct by category
          Simon Runc

          Hi Xinyi,

           

          So one way that might work for you is as follows....

           

          First we want to only take the 1st value from each Category/Key combination. We can do this with an LoD

          [Final Category]

          {FIXED [Key]: MIN([Category])}

           

          This gives you the 2nd table...which you can see on the "working" tab

           

          Next we want to only COUNT a Key if it's Category and Final Category match (else NULL, meaning it won't get counted in a COUNTD)

          [Key to Count]

          IIF([Category]=[Final Category],[Key],NULL)

           

          and then we can bring in Category and COUNTD([Key to Count]) to get your final result. Although I make it A = 2, B = 0, and C = 1 (there are 2 distinct Keys for Final Category = A)

           

          Hope that helps and makes sense.

          • 2. Re: Counting distinct by category
            xinyi.ng

            Hi Simon

             

            It worked! Thank you so much.

             

            I have a question, what does minimum(dimension var.) actually result in? The first record or the first record sorted based on alphabetical order?

             

            Regards

            Xin Yi

             

             

            • 3. Re: Counting distinct by category
              Simon Runc

              Glad it did the trick!

              ...what does minimum(dimension var.) actually result in? The first record or the first record sorted based on alphabetical order?

               

              ...if this field were numbers it would be the smallest number, but for strings, it is the first in alphabetical order (equally MAX would bring back the last), which is why the A is returned (it's just coincidence it's also the first!).

              • 4. Re: Counting distinct by category
                xinyi.ng

                Hi Simon

                 

                Understood!

                 

                Assuming I want to output the final category = category based on the minimum check code, how shld I modify the derivation of the final category?

                 

                [Final Category]

                {FIXED [Key]: MIN([Category])}

                 

                KeyCategoryCheck_CodeFinal Category
                1A1A
                1B2A
                2C1C
                2A2C
                3Z1Z
                3B2Z
                3C3Z
                • 5. Re: Counting distinct by category
                  Simon Runc

                  So assuming you always had a [Code Check] = 1, you could do it like this...

                   

                  [Final Category]

                  {FIXED [Key]: MAX(IIF([Check Code]=1,[Category],NULL))}

                   

                  So this running a row-level calculation (the IIF part) and only populating a row if  [Code Check] = 1 (else NULL). We then use a FIXED LoD to take the MAX (for each [Key])...and the MAX of something and NULL is always the something!! (in fact the MIN of something and NULL is always the something too!!).

                   

                  If we didn't always have a 1, we could generate the MIN of each Key's [Code Check] with a further LoD...so would be

                   

                  {FIXED [Key]: MAX(IIF({FIXED [Key]: MIN([Check Code]={FIXED [Key]: MIN([Check Code])},[Category],NULL))}

                   

                  a bit harder to follow!, but you can create a table and bring the parts in bit-by-bit to see what each is doing.

                   

                  Once we have this, it's the same as before....new version attached.

                  1 of 1 people found this helpful
                  • 6. Re: Counting distinct by category
                    xinyi.ng

                    Hi Simon

                     

                    Thank you so much for your help!

                     

                    I made slight changes to the code for the second scenario.

                    {FIXED [Key]: MAX(IIF(([Check Code])={FIXED [Key]: MIN([Check Code])},[Category],NULL))}