Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
AyCe1082
Creator
Creator

Select distinct value for each ID, then sum all values across IDs without ignoring duplicates?

Hi,

I had cumulative values for each player ID so I used the script to only get the max value per Game_Date as shown below. That is the max value repeated per row for one player per date.

maxtable.PNG

 

I can get the distinct for each player and sum them as a single row in a straight table with Dimensions reducing the data then using

=sum({<HOME_TEAM_NAME = , AWAY_TEAM_NAME, GAME_DATE, LAST_NAME>}aggr(Only({<PLAYERTEAMNAME2={'$(vTeam2)'},HOME_TEAM_NAME = , AWAY_TEAM_NAME = ,GAME_DATE =, LAST_NAME = >} MAX_VERY_HIGH_SPEED_TIMES),MAX_VERY_HIGH_SPEED_TIMES))

maxchart.PNG

 

My issue is that if multiple players have the same max value like above, the 'duplicates' are ignored when I try to use it as an expression to create just the total in a row (or text object).

Is it possible to take the distinct value per player per game, then sum them all without the distinct function removing duplicates?

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

May be you need this

=Sum({<HOME_TEAM_NAME, AWAY_TEAM_NAME,  GAME_DATE,  LAST_NAME>} Aggr(

Only({<PLAYERTEAMNAME2 = {'$(vTeam2)'}, HOME_TEAM_NAME,  AWAY_TEAM_NAME, GAME_DATE, LAST_NAME>} MAX_VERY_HIGH_SPEED_TIMES)

, GAME_DATE, LAST_NAME))

View solution in original post

2 Replies
sunny_talwar

May be you need this

=Sum({<HOME_TEAM_NAME, AWAY_TEAM_NAME,  GAME_DATE,  LAST_NAME>} Aggr(

Only({<PLAYERTEAMNAME2 = {'$(vTeam2)'}, HOME_TEAM_NAME,  AWAY_TEAM_NAME, GAME_DATE, LAST_NAME>} MAX_VERY_HIGH_SPEED_TIMES)

, GAME_DATE, LAST_NAME))

View solution in original post

AyCe1082
Creator
Creator
Author

Thank you very much, that is exactly what I needed.