sum(aggr(only( expression returns a 0 when used with ValueList

AyCe1082 — Mon, 09 Mar 2020 16:17:33 GMT

Hi,

I am trying to create a straight table with ValueList and some expressions:

Distance and Season Average are the ValueList Dimension.

The expressions I have is:

For Season Average:

=if(valuelist('Distance','Season Average')='Season Average',sum({<HOME_TEAM_NAME = , AWAY_TEAM_NAME>}aggr(Only({<PLAYERTEAMNAME2={'$(vTeam2)'},PRIMARY_POS_DESC={'Defender','Midfielder','Forward'},HOME_TEAM_NAME = , AWAY_TEAM_NAME = >} MAX_METERS),MAX_METERS))/

(count(DISTINCT{1<TEAM_NAME={'$(vTeam2)'},PRIMARY_POS_DESC={'Defender','Midfielder','Forward'}>} GAME_CODE)),

For Distance:

if(valuelist('Distance','Season Average')='Distance',sum(aggr(sum(DISTINCT{<PLAYER_ID=,PRIMARY_POS_DESC-={'Goalkeeper'},PLAYERTEAMNAME=[AWAY_TEAM_NAME]>}MAX_METERS),PLAYER_ID))))

The only part of the expression returning 0 is the first half of the top expression:

sum({<HOME_TEAM_NAME = , AWAY_TEAM_NAME>}aggr(Only({<PLAYERTEAMNAME2={'$(vTeam2)'},PRIMARY_POS_DESC={'Defender','Midfielder','Forward'},HOME_TEAM_NAME = , AWAY_TEAM_NAME = >} MAX_METERS),MAX_METERS))

On its own it works perfectly. It is divided by

(count(DISTINCT{1<TEAM_NAME={'$(vTeam2)'},PRIMARY_POS_DESC={'Defender','Midfielder','Forward'}>} GAME_CODE))

which also happens to work perfectly if used in the Valuelist expression with the first part commented out.

Could anyone tell me why adding this first expression to ValueList is causing the 0 result?

Re: sum(aggr(only( expression returns a 0 when used with ValueList

sunny_talwar — Tue, 10 Mar 2020 16:22:11 GMT

Look here for why this happens and possible solutions

AGGR() and Synthetic Dimensions

topic Re: sum(aggr(only( expression returns a 0 when used with ValueList in QlikView

sum(aggr(only( expression returns a 0 when used with ValueList

Re: sum(aggr(only( expression returns a 0 when used with ValueList