Conditional Counts

[#1]

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!

[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?

[swuehl]

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)

[#4]

swuehl, to obtain the NUM_GOALS I need to do a count, that's my problem... I need to do a Count inside a another count

[#5]

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?

[swuehl]

Then try like

Count({<Players = {"=Count(DISTINCT ID_Game)>1"} >} DISTINCT Players)

[#7]

I'm getting 0 Why have Players (The ID of my player) = to a Count?

[swuehl]

First, take care to use the correct field names in my above suggested expression. If the field is named ID_Player, use ID_Player instead of Players. Note that Qlik uses case sensitive field names.

I used set analysis in my expression, which I think is more readible than advanced aggregation in this case (with my limited understanding of this case, though).

The set modifier {"=Count(DISTINCT ID_Game)>1"} uses an advanced search, basically it's calculating Count(Distinct ID_Game) for each Players value (again, use ID_Player if that's your field name) and compare if the count is larger one. If the comparison is true, assign this Players value to the set used for the aggregation function the set analysis is used in.

[swuehl]

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.

[martinpohl · Accepted solution]

see attached

The set analysis sums the goals for each player. than the player will be only counted if the number of goals is grather than the limit

and

Regards