Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
martinpohl
Partner - Master
Partner - Master

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

View solution in original post

9 Replies
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
MVP
MVP

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)

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

Then try like

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

Not applicable
Author

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

swuehl
MVP
MVP

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
MVP
MVP

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
Partner - Master
Partner - Master

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