4 Replies Latest reply on Jul 4, 2018 5:41 AM by Simon Runc

    help showing unique number

    Imran Azam

      Hi Guys

       

      i am trying to do a bar chart which shows the unique numbers on investors each company has, by unique investor i mean , an investor that one company has and none of the other company has this investor

       

       

      i have attached an example report which i am working on

       

      the end out put show the numbers below on a bar chart

       

      A should show 1 ( A is the only one to have investor  D1 )

      B should show as 0 ( B has no investors that other company doesn't have)

      C should show 2 ( C is the only one to have investor  F1 and z1 )

      D should show 2( D is the only one to have investor  T1 AND Y1 )

       

      what is the best way to do this?

        • 1. Re: help showing unique number
          Simon Runc

          hi Imran,

           

          So one way I think does the job is like so...

           

          First I want to pick up how many Companies are Investing in each investment (I use an LoD for this, so I create a real dimension)

           

          [Companies Investing]

          {FIXED [Investors]: COUNTD([Company])}

           

          I can then just pick up the investments, where this is 1

          [Company Unique Investing]

          IF [Companies Investing] = 1 THEN [Investors] END

           

          and then we can use a COUNTD on this field.

           

          Depending on your actual data, and what you want to do, you could just bring [Companies Investing] on the filter shelf and set to 1...and then just use number of records

           

          There is a "How it works" tab, showing the working in table form, and then a "final" tab showing the final viz

           

          Hope that does the trick and makes sense

          • 2. Re: help showing unique number
            Imran Azam

            thanks it worked woohoooo

            • 3. Re: help showing unique number
              Imran Azam

              Hi Simon ,

               

              if i wanted to compare the unique number of investors between 2 companys that are selected from a filter,

               

              can this be done?

              • 4. Re: help showing unique number
                Simon Runc

                Yes we can do that, although with the way filters work we can't restrict people to only select 2 (we can choose 1 or all, but not insist on 2), but if they select 2, we can just re-arrange the chart and then use the quick table calculation on difference (or Percent Difference) and set Compute Using to "Company", we can do this

                 

                 

                I've done a version in the attached, but there are multiple ways you could lay this out and show the change.