Skip to Content
Former Member
Sep 19, 2014 at 10:48 AM

Aggregation for unique entires


Hello Experts,

I have requirement to show the number of complaints against Category.

Basically, my data looks like as follows:

Transaction Number Category ID 1 Number 8000000008 Billing 1 8000000008 Metering 1 8000000009 Collections 1 8000000009 CMP 1 8000000010 Metering 1

I want to have table like below:

Category ID 1 Number Billing 1 Metering 2 Collections 1 CMP 0

Here, for transaction number 8000000008, there are two categories Billing and Metering and my requirement is to have a table with just Category and Number and for this transaction number either of the category should show 1. Similarly, if there are any other transaction numbers for that category it should those as well.

Basically, it should not consider the duplicate transaction numbers.

I have tried formula - Previous but it works when you have transaction number in the layout. If transaction number is removed from layout. it gives me #Multivalue error.

Any immediate help would be appreciated.

Thanks in advance!