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

    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

        • Count Companys vs Client by region
          Vlad Gutkovsky

          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

              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
                  Vlad Gutkovsky

                  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

                    I will send a example to better understanding.

                    I hope you can help.

                    Thanks in advance.

                     

                • Count Companys vs Client by region
                  Vlad Gutkovsky

                  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,