Finding max value regardless of filter

[chaorenzhu]

Hi,

A very simple question but I don't know where it went wrong.

It's very similar to this post: https://community.qlik.com/t5/QlikView-App-Dev/Max-value-of-a-dimension-aggregated-by-another-dimens...

To use some simple data:

load * inline[

year,category,number

2019,A,10

2019,A,5

2020,A,7,

2019,B,6,

2019,B,-7,

2020,B,-3];

I want to find the max value of sum(number) per year, which is:

max(total<year>{<category>} aggr(sum({<category>} number),year,category))*avg(1)

So far so good, even though I applied filter on category it still gives me the max value I want (15 for 2019; 7 for 2020).

However, because in my case there is always negative value for sum(number), and there is this requirement that sum(number) to be presented in non-negative format, I use :

sum(number)-min(total<year>{<category>} aggr(sum({<category>} number),year,category))*avg(1)

to convert to non-negative first (and create a master measure called "non_negative" for the above calculation), before finding the max value, and here comes the problem.

Now when I apply the expression:

 max(total<year>{<category>} aggr(non_negative,year,category))*avg(1)

after filtering on category and choosing B, it returns 0.

Is there any qualifier missing? How can I get 16 for 2019 and 10 for 2020 even when applying filter?

Thank you

[Or]

---

@chaorenzhu wrote:

However, because in my case there is always negative value for sum(number), and there is this requirement that sum(number) to be presented in non-negative format, I use :

---

Could you explain this one more clearly? How did a regular sum(Number) and min(stuff) come into play here when you're looking for the max of sum(Number) by year? What do you mean by "displayed in a non-negative format"?

[chaorenzhu]

What I’m trying to do is:

Now there is a table
year category sum(number)

2019 A 15

2020 A 7

2019 B -1

2020 B -3

I’d like to find the max value by year and divide by the max value to convert sum(number) in the form of 0 to 1. Because there are negative values here, I’d like to minus each sum(number) by the minimum of sum(number) by year (which is always negative). Then find the maximum of the new column. In other words, I’m expecting:

year category new_sum

2019 A (15-(-1))/(15-(-1))=1

2020 A (7-(-3))/(7-(-3))=1

2019 B (-1-(-1))/(-1-(-1))=0

2020 B (-3-(-3))/(-3-(-3))=0

Obviously my example is just a simplification of real scenario where there are hundreds of possible values of category and my expectation is to convert all sum(number) into 0 to 1, where the maximum per year will be 1 and the minimum per year will be 0. 
Hope that will clarify a bit of my intention

[Or]

I'm still struggling to follow along, perhaps because this second post doesn't seem to match what you described in the first post.

"I’d like to find the max value by year and divide by the max value to convert sum(number) in the form of 0 to 1."

A number (Max value) divided by itself is always going to be 1, unless max value is exactly 0, so why do you need to go through the process of finding a minimum instead of just checking whether the expression evaluates to a non-zero number?

At least in the simplified example, it seems you want to take your previous working expression and zero out the result if the value is negative, in which case you could do this with a simple numeric formula, e.g. rangemax(YourFormula,0) - which would result in all negative values being replaced by zeroes. 

If that's not what you're after, then I guess I don't understand the process / required result...

[chaorenzhu]

Hi,

Sorry there was a typo in my earlier calculation formula which may confused you. It should be:

year category new_sum

2019 A (15-(-1))/(15-(-1))=1

2020 A (7-(-3))/(7-(-3))=1

2019 B (-1-(-1))/(15-(-1))=0

2020 B (-3-(-3))/(7-(-3))=0

Anyway I have fixed it by adding a qualifer in my expression as:

 max(total<year>{<category>} aggr({<category>} non_negative,year,category))*avg(1)

Basically I'm trying to do is because there could be category C,D,E, etc, with different values of sum(number), I'd like to convert them into something in the interval of [0,1], such as A (which has the max value)=1, B(which has the min value)=0, C=0.2, D=0.6, E=0.7, etc.

The reason for not just converting negative to 0 is because I'd like to use rank() to come up with ranking for all categories, hence I did the subtraction part.