topic Re: Is it possible to use Max and Sum together in Qlik sense expression in Visualization and Usability

Is it possible to use Max and Sum together in Qlik sense expression

Anonymous — Thu, 02 Feb 2017 03:42:15 GMT

I have an expression as: Sum(my_value)/my_area to calculate the recording per area.

I would like to normalize the above to 0 - 1. However, the following does not work.

(Sum(my_value)/my_area) / max(Sum(my_value)/my_area)

The issue comes from Max(Sum(my_value)/my_area).

How could I use Max and Sum in the same expression?

How could I normalize Sum(my_value)/my_area to 0 -1 ?

Any suggestion is appreciated!

sunny_talwar — Thu, 02 Feb 2017 04:00:13 GMT

When doing an aggregation over an aggregation, you need to use Aggr() function

(Sum(my_value)/my_area) / Max(TOTAL Aggr(Sum(my_value)/my_area), YourDimensions)

Use dimensions where you are performing the Sum(my_value)/my_area calculation

Anonymous — Thu, 02 Feb 2017 04:05:18 GMT

Hey Sunny, thanks so much for your help!

I figure out an expression:

max(aggr(( sum (my_value)/my_area), the_dimensions))

Let me try both your expression and my expression.

Anonymous — Thu, 02 Feb 2017 04:31:38 GMT

Hey Sunny, your expression works.

One more question:

I would like to put the above ratio to map expression:

if (((Sum(my_value)/my_area) / Max(TOTAL Aggr(Sum(my_value)/my_area), YourDimensions))>0.4, red(), blue())

however, seems not working.

Any further suggestion?

Thanks!

Anonymous — Thu, 02 Feb 2017 04:41:18 GMT

Hey Sunny, I fix that map issue. Thanks!

Sarye — Fri, 03 Apr 2020 12:41:57 GMT

I had the same issue, and I figured out I mis-wrote the parenthesis. YourDimensions is a parameter of Aggr() and not of Max().