Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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!

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

5 Replies
Highlighted

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

View solution in original post

Highlighted
Specialist
Specialist

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.

Highlighted
Specialist
Specialist

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!

Highlighted
Specialist
Specialist

Hey Sunny, I fix that map issue. Thanks!

Highlighted
Contributor II
Contributor II

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