5 Replies Latest reply: Oct 24, 2011 10:37 PM by Ram S RSS

    Limiting Count.

      I have the following fields,Participant ID, Country  and Month. I want to find total number of participants per country.

       

      Using the following  Definition ( Count (DISTINCT PARTICIPANT_ID) )  I could able to extract relevant information. My problem is in certain countries we have only 1 or 2 particpants. In certain countries we have more than 1000 participants. I want to extract only > 25 participants in chart and anything <25 should be summarised under Others.

       

       

      How do I implement this? (Attached file contains teh data, Country, Participants) It is difficult to see as we have more countries.

       

      Thanks

        • Re: Limiting Count.
          Miguel Angel Baeyens de Arce

          Hi,

           

          I'd try the following as an expression

           

          Count({< PARTICIPANT_ID = {"=Count(DISTINCT PARTICIPANT_ID) > 25"} >} DISTINCT PARTICIPANT_ID)
          

           

          And even better creating this accumulation in the script using Peek() and Previous() as a flag field, (with values 1 if greater than 25 or 0 if less), so although the load time would be a bit slower, the chart rendering will do faster. Check this thread among several others on how to create accumulations in the script using those functions.

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

          • Limiting Count.
            Anand Chouhan

            Hi,

             

            I suggest you have to use the condition in expression like

             

            Count(If( Count (Distinct PARTICIPANT_ID) ) > 25, Count (Distinct PARTICIPANT_ID) ) ) )

            this will show only > 25 participants like ways

             

            Count(If( Count (Distinct PARTICIPANT_ID) ) < 25, Count (Distinct PARTICIPANT_ID) ) ) )

            this will show only < 25 participants like ways

               

            Rgds

            Anand

            • Re: Limiting Count.
              Miguel Angel Baeyens de Arce

              Hi there,

               

              Going even further, and although I don't recommend you to do this way because of the presumable poor performance of calculated dimensions, you could leave the expression as it is, but use the following as dimension in the chart

               

              If(Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID) > 25, PARTICIPANT_ID, 'Others')
              

               

              Check syntax, I may have missed some parentheses.

               

              Hope that helps.

               

              Miguel Angel Baeyens

              BI Consultant

              Comex Grupo Ibérica

              • Re: Limiting Count.
                Anand Chouhan

                Hi,

                 

                You can use it another ways also by aggregating the data like

                 

                Count(If( Count (Distinct PARTICIPANT_ID) ) > 25, Aggr( Count (Distinct PARTICIPANT_ID),PARTICIPANT_ID) ) ) )

                this will show only > 25 participants like ways

                 

                Count(If( Count (Distinct PARTICIPANT_ID) ) < 25, Aggr( Count (Distinct PARTICIPANT_ID),PARTICIPANT_ID) ) ) )

                this will show only < 25 participants like ways

                 

                Rgds

                Anand

                  • Limiting Count.

                    Thanks for the help

                     

                    If I change the Definition

                     

                    From:   Count (DISTINCT PARTICIPANT_ID) 

                     

                    To:   If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID))

                     

                    I am getting the Graph but it is not summerised under Others.

                     

                    Please refer to the below Image fb1.JPGfb1.JPG

                     

                    If  change this to

                     

                    If(Count(Distinct PARTICIPANT_ID) >=25, Aggr( Count ( DISTINCT PARTICIPANT_ID),PARTICIPANT_ID) )

                     

                     

                    or

                     

                    Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID)

                     

                    I am getting Graph similar to below.  except in Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID) I am getting all countries.

                     

                    fb2.JPG

                     

                     

                    If I use

                     

                    Count({< PARTICIPANT_ID = {"=Count(DISTINCT PARTICIPANT_ID) > 25"} >} DISTINCT PARTICIPANT_ID)

                     

                    I am getting answer no data to display.

                     

                     

                    if I use 

                     

                    If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID), 'Others')

                     

                    Fb3.JPG

                     

                     

                    I think we should modify the folloing

                     

                    If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID))

                     

                    to add additional Row 'Others' and it should be incremented in condition is met. How should I implement?

                     

                    Thanks