6 Replies Latest reply: Sep 3, 2010 3:44 PM by Vlad Gutkovsky

# Count Companys vs Client by region

Hello,

I am having some problems to calculate a chart where i can compare number of companys vs number of clients per Region.

Example:

Companies:

C1 Rergion A

C2 Region B

C2 Region B

Clients:

1 Region A

2 Region A

3 Region B

4 Region C

My final chart would have something like this:

Regions N. Companies N. Clients

Region A 1 2

Region B 2 1

Region C 0 1

I have also made Region Island and i am trying to make the calculation by the Island.

Do you have any suggestion ?

Vasco Rodrigues

• ###### Count Companys vs Client by region

Just have 2 expressions in your chart: (1) count(distinct Companies) (2) count(distinct Clients). The dimension would be Region.

Regards,

• ###### Count Companys vs Client by region

That's was one of my first approach and the first step to see the problem....

My model is like:

I have the table company linked to other tables and also linked one region table (region_company);

I have the table client linked to other tables and also linked to other region table (region_client);

I can't link the regions because in that case i would have a cyclic data model.

For dimension if i put the region_company as dimension then the counter counts ok for region but not for clients. If i make the region_client as a dimension of a chart it counts ok the clients but not the companies. That's why i am trying to create a island and do some Set Analysis to do the trick.

Any suggestion ?

• ###### Count Companys vs Client by region

I would recommend the following: create a separate field just called "Region" that would contain all the region values. If linking would cause a cycle, then just have this table as an island (not linked to anything). Then create a chart with that new Region field as a dimension and the following 2 expressions:

`count(distinct if(region_company=Region,Companies))count(distinct if(region_client=Region,Clients))`

Depending on how many rows you have, this may get a little heavy. But give it a try. The problem is that the Region field would not get updated with user selections if it's an island. If that's a problem, then you need to redesign your data model, I think.

Regards,

• ###### Count Companys vs Client by region

That makes the trick!

Many thanks,

Vasco

• ###### Count Companys vs Client by region

I will send a example to better understanding.

I hope you can help.