Divide a Row Value by Maximum Column Value

ben2r — Sat, 16 Nov 2024 04:58:02 GMT

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:

Category	Time	Calls	Max Calls
Discussion	2017: Jan	761	-
Discussion	2017: Feb	832	-
Discussion	2017: Mar	897	-
Discussion	2017: Apr	618	897
Discussion	2017: May	808	-
Discussion	2017: Jun	813	-
Discussion	2017: Jul	663	-
Discussion	2017: Sep	727	-
Discussion	2017: Oct	763	-
Discussion	2017: Nov	704	-
Discussion	2017: Dec	574	-
	Total	8,160	897

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

Re: Divide a Row Value by Maximum Column Value

sunny_talwar — Thu, 13 Dec 2018 17:02:49 GMT

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]))

Re: Divide a Row Value by Maximum Column Value

ben2r — Mon, 17 Dec 2018 14:09:16 GMT

Excellent - Sunny my hero once again.

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

topic Divide a Row Value by Maximum Column Value in QlikView

Divide a Row Value by Maximum Column Value

Re: Divide a Row Value by Maximum Column Value

Re: Divide a Row Value by Maximum Column Value