Aggregation for unique entires

[Former Member]

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!

Regards

Lavanya

[former_member210032]

Hi Jutla,

Category ID  Number

Billing	1
Metering	1
Collections	1
CMP	0

if your output like this see the below screenshot

Category ID   Number

Billing	1
Metering	2
Collections	1
CMP	0

if your output like this see the below screenshot

Regards

Mustafa

[former_member210032]

Hi Jutla,

Create variable =max([number]  see the below screenshot

Regards

Mustafa

[Former Member]

Hi Mustafa,

This too doesn't work

[former_member201488]

Why does the CMP category have a zero in your ideal output table? Surely this should be a one, due to transaction 8000000009?

[Former Member]

Hi Neil,

My requirement is to display 1 for each transaction number even though the same transaction number has two different categories.

Any idea how can I achieve this?

[former_member201488]

Sorry, your requirement is still vague.

Looking at your example data, Transaction 8000000008 references two categories, and they both seemed to be reflected in your summarised output (metering & billing) However, Transaction 8000000009 also references two categories (collections & cmp), yet only one is referenced in the summarised output - cmp is zero?

If you only want to count each Transaction number once, irrespective of Category, then how do you decide which Categories to include and which to exclude? Alphabetical order?

[Former Member]

Hi Neil,

Excluding any of the category should be ok.

Do you have any idea how to achieve this?

[former_member201488]

No, because you don't indicate the basis for the exclusion - how do you decide what to exclude and what to include?

[Former Member]

Hi Neil,

Sorry, it seems i have given wrong output.

The output should be:

Billing	1
Metering	1
Collections	1
CMP	0

[former_member201488]

You're still not explaining the basis. To me, it looks like you're counting the FIRST Category ID from each Transaction, but there is no logic to their order - they're not in Alphabetical order, otherwise CMP would come before Collections for Transaction 8000000009.

If you don't explain the reasons behind your requirement, people are going to waste their time and yours suggesting mechanisms that may not be appropriate. Please save us all a lot of time and effort by clearly stating your requirements, with examples and how they are derived.

[Former Member]

Hi Neil,

Please consider the categories to be taken in Alphabetical order.

Thanks.

[Former Member]

Hi Lavanya,

You can create a block as follows:

Category Id    Number1

[Number1] could be defined as follows:

=Max([Number] ForEach ([Transaction Number]) )

This should display one Number value per transaction per Category.

Try and check if it helps.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

This does not work.

Any other idea?

Thanks

Lavanya

[former_member203850]

As you mentioned above , your data looks like :

Here declare Transaction number as dimenion to avoid aggregation at report level.

And Number field as measure and aggregation function : Sum

Once you remove Transaction number object from the report layout, then your data will looks like as follows:

Please check and let me know how it goes.

Regards,

Pranay

[Former Member]

Hi Yuvraj,

This solution is not working.

Any other idea please?

[saurabh_sonawane]

=count(Category ID foreach (Transaction Number))

or

=count(Category ID IN (Transaction Number))