# Count Function

Options

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
Tagged:

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

• Options

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

• Options

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

• Options

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

• Options

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

• Options

Did you follow my set up method from the other post

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

David

• Options

Hi Uma,

Its not like you need dimension to apply filter.

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

~Vignesh

• Options

Hi vignesh,

Yes, its working fine.

Thank you so much.

Regards,

Uma

• Options

Hi David,

Its working fine.

Regards,

Uma

• Options

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

AB

• Options

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

• Options
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
• Options
no worries.. I got the formula.. we can use MAXIMUM instead of SEQUENTIAL

Thanks
• Options

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

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