4 Replies Latest reply on Oct 25, 2016 10:07 AM by David Li

    Count Distinct HELP

    Mikey Michaels

      Hello All,

      quick question- I was able to place my data into Bins (thanks to this forum!), however, I'm struggling with finishing my viz. Instead of listing each Bin, country, and count, I just need the Bins and the Total total count -> meaning for Bin 26-50 I need to only show 8, not each individual country.

       

      I hope this makes sense. See attached.

       

      Cheers,

      Mikey

       

        • 1. Re: Count Distinct HELP
          Michael Lowden

          I'm struggling with similar right now. COUNT and COUNTD seem to only and always show "1". Really frustrating.

          • 2. Re: Count Distinct HELP
            Luciano Vasconcelos

            Hi.

            Is this what you need?

             

            1 of 1 people found this helpful
            • 3. Re: Count Distinct HELP
              Michael Lowden

              I'm looking at your code and what you're essentially after is a multi-layered aggregation. You want to know COUNTDs for each country, and then group by those COUNTD SUMs.
              Argentine: <50
              Canada: <50
              USA: >500

               

              ... and at the end you want to see????
              <50 : 2

              >500 : 1

               

              ????

              1 of 1 people found this helpful
              • 4. Re: Count Distinct HELP
                David Li

                Hi Mikey, is this what you need?

                The problem was that your bins were an aggregated calculation, so they actually can't contribute to the level of detail. Only record-level calculations can. So I changed your Bins calculation to use LOD calculations so it could live at the record level. I created a calculated field, [Record Count]:

                { FIXED [Country] : SUM([Number of Records]) }

                Then, I binned on that:

                IF [Record Count] > 1000 THEN ">1000"

                ELSEIF [Record Count] > 500 THEN "501-1000"

                ELSEIF [Record Count] > 250 THEN '251-500'

                ELSEIF [Record Count] > 100 THEN '101-250'

                ELSEIF [Record Count] > 50 THEN '51-100'

                ELSEIF [Record Count] > 25 THEN '26-50'

                ELSE "<=25"

                END

                The disadvantage of this is that it only bins for Country. It won't bin if you change the dimensions.

                 

                After doing this, you could also take Country out of the rows shelf and just use CNT(Record Count) to get the total count.

                2 of 2 people found this helpful