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

# Conditional Counts

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

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

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

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

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)

• ###### Re: Conditional Counts

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

• ###### Re: Conditional Counts

Then try like

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

• ###### Re: Conditional Counts

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

• ###### Re: Conditional Counts

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.

• ###### Re: Conditional Counts

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