Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
datalakeyu
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
sunny_talwar

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
sunny_talwar

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

datalakeyu
Specialist
Specialist
Author

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.

datalakeyu
Specialist
Specialist
Author

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!

datalakeyu
Specialist
Specialist
Author

Hey Sunny, I fix that map issue. Thanks!

Sarye
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().