Register

## Count Function

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
Certified Master Anaplanner

## RE: Count Function

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

## Re: RE: Count Function

Hi,

I have a similar requirement.

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.

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

Please suggest me how to resolve the above requirement?

Regards,

Uma

Master Anaplanner/Community Boss

## Re: RE: Count Function

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

Certified Master Anaplanner

## Re: RE: Count Function

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

Contributor

## Re: RE: Count Function

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

Master Anaplanner/Community Boss

## Re: RE: Count Function

Did you follow my set up method from the other post

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

David

Contributor

## Re: RE: Count Function

Hi David,

Its working fine.

Regards,

Uma

Certified Master Anaplanner

## Re: RE: Count Function

Hi Uma,

Its not like you need dimension to apply filter.

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

~Vignesh

Contributor

## Re: RE: Count Function

Hi vignesh,

Yes, its working fine.

Thank you so much.

Regards,

Uma