Count Function

[PrevContributor]

Hi, I have a line item 'country' based on which my account module is developed. These accounts are mapped to countries. I need to find out the count of accounts from each country. In the sample below, there are 5 US accounts, 2 Canada and so on. How to come up with the count of UNIQUE accounts based on each country? for the below example I want to find out only Account 1, Account 2 and Account 3 for US country . So for US the Accounts count should be 3.

Accounts	Country
Account 1	US
Account 2	US
Account 3	US
Account 1	US
Account 1	US
Account 6	Canada
Account 7	Canada
Account 8	Mexico
Account 9	Mexico
Account 10	Mexico

[CommunityMember111277]

Hi,

Just to add, lets say I have a account which is appearing in both countries - US & Canada(Module 1). Using the Count function - I have identified the account which is appearing multiple times (Module 2). But, how to mark the account(Account 4)in module 1 using module 2 findings - I can create a new line item (Boolean) in module 1, but I'm unable to identify the correct formula to fetch the details from Module 2 to 1

Accounts	Country
Account 1	US
Account 2	US
Account 3	US
Account 4	US
Account 5	US
Account 6	Canada
Account 4	Canada
Account 8	Mexico
Account 9	Mexico
Account 10	Mexico

Thanks

AB

[JussiLi]

@CommunityMember111277,

One way to do it is to use RANK function. In this example 'Account occurrence' line item shows number1if account occurs more than once.

Then conditional formatting is used in Accounts line item. This doesn't mark the first occurrence of account, but all other (second, third,...).

Regards,

JussiLi

[CommunityMember111277]

Hi JussiLi,

Thanks for the suggestions.. Really appreciate it.
在这种情况下,Account-4 (Canada) is getting highlighted under "Account occurrence". However, if I need to highlight both Account-4 Canada & US under "Account Occurrence"- then what is the solution?

Thanks
AB

[CommunityMember111277]

no worries.. I got the formula.. we can use MAXIMUM instead of SEQUENTIAL

Thanks

[JussiLi]

@CommunityMember111277,

I would use separate module which stores the number of accounts data:

and bring account count data back to main module and use it in conditional formatting:

It is possible to replace separate 'Account count' module with a line item which has Accounts as a dimension and use same IF statement there. But it is best practice to avoid subsidiary views.

Regards,

JussiLi

[CommunityMember111277]

Thanks.. It worked.. BIG KUDOS to you.