Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ben2r
Contributor III
Contributor III

Divide a Row Value by Maximum Column Value

Dear all, I am trying to create a calculation that divides a row value in a table by the maximum value within that column.

My table looks like this:  

CategoryTimeCallsMax Calls
Discussion2017: Jan761-
Discussion2017: Feb832-
Discussion2017: Mar897-
Discussion2017: Apr618897
Discussion2017: May808-
Discussion2017: Jun813-
Discussion2017: Jul663-
Discussion2017: Sep727-
Discussion2017: Oct763-
Discussion2017: Nov704-
Discussion2017: Dec574-
 Total8,160897

 

Calls is a variable:

COUNT(DISTINCT [Call ID])

Max Calls is a variable:

AGGR(MAX(AGGR(COUNT(DISTINCT [Call ID]),[Created Month])),category)

What I am trying to do is divide each row by the maximum value. I.e. based on above, row 1 would be 761/897. 

I cant work out how to calculate this maximum value for the table then use it across all values. Any other way I have tried leads to the max value being the same as the count of calls. 

Thanks,

Ben

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Try one of these

AGGR(NODISTINCT MAX(AGGR(COUNT(DISTINCT [Call ID]),[Created Month])),category)

or

MAX(TOTAL <category> AGGR(COUNT(DISTINCT [Call ID]),[Created Month]))

View solution in original post

2 Replies
sunny_talwar

Try one of these

AGGR(NODISTINCT MAX(AGGR(COUNT(DISTINCT [Call ID]),[Created Month])),category)

or

MAX(TOTAL <category> AGGR(COUNT(DISTINCT [Call ID]),[Created Month]))
ben2r
Contributor III
Contributor III
Author

Excellent - Sunny my hero once again. 

I have been struggling with this for ages. I was not familiar with the no distinct command!