9 Replies Latest reply: Mar 20, 2016 1:20 PM by Martin Pohl RSS

    Conditional Counts

    A v

      Hello,

       

      I need to count the numbers of players per Team who already have one or more goal.

       

      Basically I want to do the following calculation:

       

       

      =AVG(AGGR(COUNT( (IF(COUNT(NUM_GOALS)>1) DISTINCT Players), TEAM))

       

      I can do this doing a external table in load script but I think is a good practice

       

       

       

      Thanks!

        • Re: Conditional Counts
          Sunny Talwar

          I can do this doing a external table in load script but I think is a good practice

           

          Not sure what you have and what you mean when you say the above statement? Do you have a sample you can share?

            • Re: Conditional Counts
              A v

              Hi Sunny T,

               

              Can't share Sorry.

              Player:

              ID_Player,

              Date,

              ID_Game,

              Goals

               

              Imagine this:

               

              Player:

              ID_Player,

              Date,

              ID_Game,

              Goals

               

               

               

              I want to count the numbers of ID_PLayers that have count(distinct ID_Game)>1. That helps?

                • Re: Conditional Counts
                  Stefan Wühl

                  A v wrote:

                   

                  Hi Sunny T,

                   

                  Can't share Sorry.

                  Player:

                  ID_Player,

                  Date,

                  ID_Game,

                  Goals

                   

                  A v, I believe it should be possible to post more details about your data, you don't need to post the real data, just some sample / mock up records that demonstrate how your data look like, how fields are related.

              • Re: Conditional Counts
                Stefan Wühl

                A sample would definitely help.

                 

                Just guessing:

                 

                Count({<NUM_GOALS = {">0"} >} DISTINCT Players)

                 

                could return the number of players with goals. Use this as expression in a table with dimension Team to get the count grouped by team.

                 

                If you want to get the average across all teams:

                 

                =Count({<NUM_GOALS = {">0"} >} DISTINCT Players) / Count(DISTINCT TEAM)