Count Function

PrevContributor
Previous Contributor

Count Function

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
15 REPLIES 15
CommunityMember111277
Frequent Contributor

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

 

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

 

Thanks

AB

JussiLi
Frequent Contributor

@CommunityMember111277 ,

 

One way to do it is to use RANK function. In this example 'Account occurrence' line item shows number 1 if 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,...). 

JussiLi_0-1596647045953.png

Regards,

JussiLi

CommunityMember111277
Frequent Contributor

Hi JussiLi,

Thanks for the suggestions.. Really appreciate it.
In this case, 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
Frequent Contributor

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

Thanks
JussiLi
Frequent Contributor

@CommunityMember111277 , 

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

JussiLi_0-1596651599795.png

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

JussiLi_1-1596651718798.png

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
Frequent Contributor

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