CODEWITHSUNDEEP
maxaggregateqlikview
Yoni Green
Yoni Green

Reputation: 37

Qlikview Max Aggr Value

I'm trying to present a max sum(duration) value out of 6 different machines with different sum(duration): Machine 1: 200 Machine 2: 300 Machine 3: 400 Machine 4: 500 Machine 5: 600 Machine 6: 700

When I use: Max(total aggr(Sum(Duration),MachineID)) this is the result:

Machine 1: 700 Machine 2: 700 Machine 3: 700 Machine 4: 700 Machine 5: 700 Machine 6: 700

If I use Max(aggr(Sum(Duration),MachineID)) same as before but without "total", I get this:

Machine 1: 200 Machine 2: 300 Machine 3: 400 Machine 4: 500 Machine 5: 600 Machine 6: 700

My desired result is a graph showing only one max value: Machine 6: 700

Can you help? Thanks!

Upvotes: 1

Views: 3139

Answers (1)

bdiamante
bdiamante

Reputation: 17550

In Qlik, I believe the only way to accomplish this is to use calculated dimensions. The idea is to set up a dimension that only displays values when a condition is met. In your case, this condition is when a Machine's duration is the maximum of all machines.

Calculated Dimension (indented to provide comments):

=if(
   //check each machine's sum(duration) against the maximum sum(duration) for all machines
   aggr(sum(Duration),MachineID) >= max(total aggr(sum(Duration),MachineID)), 

   //if >= max, use MachineID as the dimension
   MachineID, 

   //if < max display nothing
   null()
)

Uncommented (copy/paste) version:

=if(aggr(sum(Duration),MachineID) >= max(total aggr(sum(Duration),MachineID)), MachineID, null())

One key to this is to make sure you check the 'Suppress When Value is Null' checkbox on your dimension. This is required so we can force this calculated dimension to act as a filter.

Another tip is to go to the Presentation tab of your table/chart and select the calculated dimension you created and check the 'Hide Column' radio button. Since we are using the calculated dimension only as a filter and not as a practical column name, we can hide it.

When you're finished, your dimensions will be: MachineID and the calculated dimension outlined above. Your expression/measure will be simply sum(duration).

I've found Qlik to be generally weak in the area of filtering by expression output/value. The above process does work, but for something that is very common in many fields, this becomes overwhelming when you have multiple calculated dimensions like this in one chart.

See here for a similar question using sliders to set thresholds instead of the max.

Upvotes: 2

Related Questions