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

[AyCe1082]

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?

[sunny_talwar · Accepted solution]

Look here for why this happens and possible solutions

AGGR() and Synthetic Dimensions