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

[pierre_kerkinni · Accepted solution]

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.

[UMAMAHESH]

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?

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

[UMAMAHESH]

Hi David,

Thank you for your reply.

Its working fine.

Regards,

Uma

[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