Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

datalakeyu
Valued Contributor

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
MVP
MVP

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

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

4 Replies
MVP
MVP

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

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

datalakeyu
Valued Contributor

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

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
Valued Contributor

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

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
Valued Contributor

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

Hey Sunny, I fix that map issue. Thanks!