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

# How to count unique ID's?

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?

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?

See Sheet2 in the attached.

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

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?

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?

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?

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?

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?

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?

Thanks a lot. Awsome.