9 Replies Latest reply on Aug 12, 2016 7:54 AM by Luciano Vasconcelos

    How to count unique ID's?

    Aaron Lum

      In the attached workbook, I show a table with the order date, the order ID and the count of orders with that same order ID. How can create a counter (I assume calculated field) that will count unique order ID's. Would LOOKUP() or COUNTD() be involved in this?

        • 1. Re: How to count unique ID's?
          Kent Sloan

          Hi Aaron,

           

          I might be missing something here but to get a count of the number of unique Order IDs go to Analysis>Create Calculated field and enter e COUNTD([Order ID]). Drop this calculation on Columns, remove SUM(Count) and Order ID then sort to see which days had the most unique orders placed.

           

          Thanks,

          Kent Sloan

          InterWorks

          • 2. Re: How to count unique ID's?
            Joe Oppelt

            See Sheet2 in the attached.

            • 3. Re: How to count unique ID's?
              Joe Oppelt

              You'll notice the number showing up in 2 places.  The table calc is computed for each row, so you'll get multiple copies (one per row) in the last column.

               

              I also duplicated it, dragged the copy to the rows shelf.  Initially it goes on as a green (measure) pill, but then I changed it to DISCRETE (blue pill).  In that format I could move it forward in the list of ROWS pills.  Putting it after [Order Date] makes it display once per [Order Date].

              • 4. Re: How to count unique ID's?
                Aaron Lum

                Thanks again Kent Sloan, I think the example I used was slightly simplified because that explanation worked perfectly!

                 

                I'll try to explain the situation clearly and if it doesn't make sense, I'll attempt to create a sample workbook that will help illustrate it"

                1. I'm making a counter for a subset of the data (so to make my previous workbook more accurate to this situation - I should've said I was trying to make a unique counter of a subset of the Order ID's e.g. I want to find the number of unique Order ID's > 100000).
                2. I cannot put a filter on this date because I need the whole data set to satisfy a workaround I'm using for another sheet in the workbook.
                3. To achieve this filtering effect, I'm using a calculated field as a makeshift filter (i.e. if condition satisfied then 1, else 0, end)

                 

                So given these parameters, I need to count the unique ID's.

                • 5. Re: How to count unique ID's?
                  Joe Oppelt

                  In the attached I am counting only those Order IDs that start with US.


                  you can apply the same principle to your order IDs in your actual workbook.

                  • 6. Re: How to count unique ID's?
                    Aaron Lum

                    Hi Joe Oppelt, thanks for your help but I think we might have a misunderstanding - I'm not sure how that helps count the number of unique Order ID's. My other reply in this thread expands on this.

                    • 7. Re: How to count unique ID's?
                      Kent Sloan

                      Hi Aaron,

                       

                      Still not sure exactly what you are looking for. In the attached example I have set up logic so that for I am taking the COUNTD() of orders that have a combined total sales above some amount I set by a parameter. This logic will ignore filters added to the worksheet unless they are placed in context. I suspect that what you are trying to do will require a LOD calculation. If you are not familiar with these I would highly recommend taking the time to look over the article linked below and download the examples to try and recreate what they have done to give yourself a solid understanding of how these can be used.

                       

                      Top 15 LOD

                       

                      Thanks,

                      Kent Sloan

                      InterWorks

                      2 of 2 people found this helpful
                      • 8. Re: How to count unique ID's?
                        Joe Oppelt

                        I did what I thought you were asking for.

                         

                        I guess I don't understand your requirement applied to the context of the sample workbook we're working with.  (The description you gave doesn't match the data in the workbook.)

                        • 9. Re: How to count unique ID's?
                          Luciano Vasconcelos

                          Thanks a lot. Awsome.