Sum the Max values of a field in a straight table to display in a row in another straight table

[AyCe1082]

Hi all,

I have a list of cumulative values in a field and I need to display the total of only their maximum values. I have successfully done that but I have to display that total as its own dimension.

I use

 =if(PLAYERTEAMNAME=HOME_TEAM_NAME and PRIMARY_POS_DESC<>'Goalkeeper',PLAYER_ID)

as the calculated dimension to only display certain IDs (otherwise the two teams are joined together, and Goalkeeper needed to be excluded), and then 

=max({<PLAYER_ID>}TOTAL_METERS)

as the expression to display the max values I wanted. The table below has the correct total.

The table below is where I would need the totals to show (please ignore the huge number, it was just the latest attempt).

I have browsed extensively and found a few topics that went unsolved and some that had elaborate solutions which were difficult to apply to my issue. I have used variables, variables combined with expressions, I even tried a for loop in the script. Unfortunately I'm still learning and nothing has worked so far.

If anyone can help, it will be greatly appreciated!

Edit: I am also looking at loading only the max values in the script as a separate field, based off the Player IDs. The data is loaded with SQL queries. Any help with this method is also greatly appreciated.

[AyCe1082]

Update: I managed to create a new field called MAX_METERS in the load script (Idea taken from https://community.qlik.com/t5/New-to-QlikView/Get-Data-with-Max-Value-for-each-ID/m-p/1227317) like so:

TotalMetersPre:
LOAD
PLAYER_ID,
TOTAL_METERS
Resident Physical;

NoConcatenate

TotalMeters:

LOAD
PLAYER_ID,
max(TOTAL_METERS) as MAX_METERS

Resident TotalMetersPre Group by PLAYER_ID Order by PLAYER_ID;

Left Join
LOAD
PLAYER_ID,
TOTAL_METERS
Resident TotalMetersPre;

Drop Table TotalMetersPre;

This seemed to almost work perfectly but one player has a different Max value now:

The value went up by 1,182 from 11,296 to 12,478. Every other ID in the shortlist matched. There is no 1,182 value in the shortlist.

[AyCe1082 · Accepted solution]

Solved. 

I am not sure if this will help anyone in future but I'll post how it was fixed just in case.

I changed the load script to:

TotalMetersPre:
LOAD
GAME_CODE,
PLAYER_ID as PLAYER_ID,
TOTAL_METERS as TOTAL_METERS2
Resident Physical;

NoConcatenate

TotalMeters:

LOAD
GAME_CODE,
PLAYER_ID,
max(TOTAL_METERS2) as MAX_METERS

Resident TotalMetersPre Group by GAME_CODE, PLAYER_ID Order by GAME_CODE;

Left Join
LOAD
GAME_CODE,
TOTAL_METERS2,
PLAYER_ID
Resident TotalMetersPre;

Drop Table TotalMetersPre;

The main difference was changing Group by to include GAME_CODE and changing Order by to GAME_CODE

Then changed the expression:

=sum(aggr(sum(DISTINCT{<PLAYER_ID=,PRIMARY_POS_DESC-={'Goalkeeper'}>}MAX_METERS),PLAYER_ID))

Gives the correct value as its own row. This does create a Syn table however. Just need to arrange a link table to remove