Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
1 Solution

Accepted Solutions
vgutkovsky
Honored Contributor II

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,

6 Replies
vgutkovsky
Honored Contributor II

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,

Not applicable

Count Companys vs Client by region

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
Honored Contributor II

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,

Not applicable

Count Companys vs Client by region

I will send a example to better understanding.

I hope you can help.

Thanks in advance.

Not applicable

Count Companys vs Client by region

That makes the trick!

Many thanks,

Vasco

vgutkovsky
Honored Contributor II

Count Companys vs Client by region

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,

Community Browser