Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 ?

Thanks in advance,

Vasco Rodrigues

1 Solution

Accepted Solutions
vgutkovsky
Master II
Master II

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,

View solution in original post

6 Replies
vgutkovsky
Master II
Master II

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

Regards,

Not applicable
Author

Thanks Vlad,

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 ?

Thanks in advance.

vgutkovsky
Master II
Master II

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,

Not applicable
Author

I will send a example to better understanding.

I hope you can help.

Thanks in advance.

Not applicable
Author

That makes the trick!

Many thanks,

Vasco

vgutkovsky
Master II
Master II

Based on your example, you might be able to avoid your cycle problems is you have fewer tables (maybe even 1 large table). Try joining Region_Client into Client, and Region_company into Company. Drop the unnecessary ID fields after the join. Then join everything into Invoices. You should then be left with 1 large table and you can add Region without worrying about synthetic keys.

See attached solution.

Regards,