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

Accepted answers

pierre_kerkinni

Hi Subhayan,

One option is using the average function to calculate the unique count of accounts by country (see attached screenshot). In the first module, I've assigned a value of 1 to each account/ country combination. In a seperate module, I've taken the average to help determine the number of unique counts. You can substitue average with the sum funciton if you need the total of each combination.

Screen_Shot_2016-05-27_at_4_1396.png

All comments

pierre_kerkinni

Screen_Shot_2016-05-27_at_4_1396.png

UMAMAHESH

Hi,

I have a similar requirement.

employee 2.PNG

Based on position,Dept,Employee Type & Location combination report shows the employees list. Here, I want to view the those employees count.

By using the formula from the above reply, i write formula as per my requirement.

But the formula was invalid because 'average' formula supports other than " list&Date " format.

employee 3.PNG employee 4.PNG

But as per my requirement "position" line item format is List.

Please suggest me how to resolve the above requirement?

Can you please suggest me?

Regards,

Uma

DavidSmith

@UMAMAHESH

the best option will be to have a numeric line item within the employee details module with the formula 1

Then you can use that to count and average

If you need to count the number of owners, managers, create a module by Position and sum the headcount value into that module

Hope that helps

David

VIGNESH.M

Hi Uma,

In Employee Module, Create 1 line ltem(Number format)

Use this Logic,

If filter=true then 1 else 0

Then take the Top level(Total) of Employee value is your Filter Count.

I have a Question,

Why do you need Position Dimesnion in your Employee module, when you already have position infomration in lIne item itslef.

~Vignesh

UMAMAHESH

Hi Vignesh,

I am using position dimension in employee module because we need minimum one dimension in page selector of employee module. if not filter is not applying, i dont know why its happening?

In page selector i didnt maintain any dimension then filter is disabled

When i maintain any one dimension in page selector then Filter is enabled.

Thats why i am using position dimension in employee module.

I am sorry I dont whether it is a correct practice or not.

Regards,

Uma

DavidSmith

@UMAMAHESH

Did you follow my set up method from the other post

You should not need the position dimension in the employee details module

David

VIGNESH.M

Hi Uma,

Its not like you need dimension to apply filter.

Remove the Position Dimension and Clear all filter, then reapply the filter.

~Vignesh

UMAMAHESH

Hi vignesh,

Yes, its working fine.

Thank you so much.

Regards,

Uma

UMAMAHESH

Hi David,

Thank you for your reply.

Its working fine.

Regards,

Uma

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

JussiLi

@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,...).

Regards,

JussiLi

CommunityMember111277

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

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.

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385