Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
afbraga1
Creator
Creator

Max value of a dimension aggregated by another dimension

Hello,

As of late, I've been trying to create a table which gives me the max value of a material regardless of the customer. If I don't make filters it works flawlessly, but when filtering it the scope changes and I it adapts to to that.

Below is the table without filters:

If I filter by material it works as I want. Image below:

But when I filter by Customer(image below), I don't get want I want, since wanted to see for A-500, B-1000, C-200.

I understand why this happens, but it's not what I want.

Is there any way to achieve what I am portraying here?

Thanks in advance,

André Braga

1 Solution

Accepted Solutions
sunny_talwar

May be this

Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)) * Avg(1)

View solution in original post

11 Replies
sunny_talwar

Try this:

Max(TOTAL <Material> {<Customer>} Value) * Avg(1)

afbraga1
Creator
Creator
Author

But I need to use the SUM() function on my Value metric. How can I do it with that? Thank you for helping!

sunny_talwar

May be this

Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)) * Avg(1)

afbraga1
Creator
Creator
Author

That's it! Thanks very much!! Could I ask you the purpose of the {<Customer>} and the Avg(1) ? I understand everything else apart from these two.

sunny_talwar

{<Customer>} is used to ignore selection in Customer field and Avg(1) is used to make sure that you only see those rows where you have selected a particular customer because if you won't use Avg(1), you will see all customers (because we ignored selection in it).

afbraga1
Creator
Creator
Author

Ah got ya, that makes sense. I was doing something like the Avg(1) but with an IF before the expression itself.

Thanks for help Sunny!

sunny_talwar

That should work too..

If(Customer, Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)))

chanin7893
Partner - Contributor III
Partner - Contributor III

Hi Sunny

I'm new to these advanced calculations using set analysis, so could you please tell me why was <Material> used in the equation

sunny_talwar

That is field name used together with TOTAL qualifier. It is saying that show the Total at the Material level for each row