Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

Hi,

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

table.PNG

 

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?

1 Solution

Accepted Solutions
Highlighted

Look here for why this happens and possible solutions

AGGR() and Synthetic Dimensions 

View solution in original post

1 Reply
Highlighted

Look here for why this happens and possible solutions

AGGR() and Synthetic Dimensions 

View solution in original post