Count Function

CommunityMember111277
Contributor

Re: RE: Count Function

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

Re: RE: Count Function

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

Re: RE: Count Function

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
Contributor

Re: RE: Count Function

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

Thanks
JussiLi
Frequent Contributor

Re: RE: Count Function

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

Re: RE: Count Function

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