topic Count Companys vs Client by region in QlikView

Count Companys vs Client by region

Fri, 03 Sep 2010 17:53:19 GMT

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

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

Count Companys vs Client by region

vgutkovsky — Fri, 03 Sep 2010 18:16:37 GMT

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

Fri, 03 Sep 2010 18:37:52 GMT

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.

Count Companys vs Client by region

vgutkovsky — Fri, 03 Sep 2010 19:32:16 GMT

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

Fri, 03 Sep 2010 19:32:51 GMT

I will send a example to better understanding.

I hope you can help.

Thanks in advance.

Count Companys vs Client by region

Fri, 03 Sep 2010 19:38:52 GMT

That makes the trick!

Many thanks,

Vasco

Count Companys vs Client by region

vgutkovsky — Fri, 03 Sep 2010 19:44:21 GMT

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,