12 Replies Latest reply: Jan 29, 2017 6:37 AM by Feras Alsuhaibani

# Selecting Top (Max) Duration For Every Status

Hello everyone!

Is there a way to tell Qlik Sense to select only max values of a measure for every dimension value? For example Assume I have a table that has ID, Desc, Duration, Type and Status (New, Accepted, Rejected, Under Processing); I'd like to get only the maximum duration for every status I have so maximum duration for the request that took the most of the time at New status, etc...

• ###### Re: Selecting Top (Max) Duration For Every Status

Maybe like this:

Create a straight table chart with dimension Status and use as expression

=Max(Duration)

• ###### Re: Selecting Top (Max) Duration For Every Status

Thanks Stefan! The suggested solution gives all rows sorted by Duration

• ###### Re: Selecting Top (Max) Duration For Every Status

Maybe I don't understand what you are trying to achieve.

The chart should show you all Status values with the corresponding max Duration value in your record set,not all rows / records.

It would be helpful if you can post a small sample set of your data records and your requested result.

• ###### Re: Selecting Top (Max) Duration For Every Status

Would you provide data which you want to know Maximum value

• ###### Re: Selecting Top (Max) Duration For Every Status

Here is a sample data:

+-------------------------------------------------------+

| ID | Desc     | Duration | Type | Status |

+-------------------------------------------------------+

| 1 | New Req |  100      |  1     |  New     |

----------------------------------------------------------

| 2 | New Req |  109      |  1     |  New     |

----------------------------------------------------------

| 3 | New Req |  125      |  1     |  New     |

----------------------------------------------------------

| 4 | Pending  |  109      |  1     |  UP      |

----------------------------------------------------------

| 5 | Pending  |  250     |  1     |  UP      |

----------------------------------------------------------

| 6 | Accept  |  250     |  1     |  Accepted      |

----------------------------------------------------------

| 7 | Accept   |  350     |  1     |  Accepted      |

---------------------------------------------------------------

Having this table, the output should be only:

+-------------------------------------------------------+

| ID | Desc     | Duration | Type | Status |

+-------------------------------------------------------+

| 3 | New Req |  125      |  1     |  New     |

----------------------------------------------------------

| 5 | Pending  |  250     |  1     |  UP      |

----------------------------------------------------------

| 7 | Accept   |  350     |  1     |  Accepted      |

---------------------------------------------------------------

I should mention that I need to get the associated data of the maximum as well (i.e. ID of the request)

• ###### Re: Selecting Top (Max) Duration For Every Status

dimension

Desc

expression

max(Duration)

FirstSortedValue(ID, -aggr(max(Duration), ID, Desc))

FirstSortedValue(Status, -aggr(max(Duration), ID, Desc))

FirstSortedValue(Type, -aggr(max(Duration), ID, Desc))

• ###### Re: Selecting Top (Max) Duration For Every Status

Can you please explain more? where should I write this?

• ###### Re: Selecting Top (Max) Duration For Every Status

In a Qlik Sense table

Desc is the dimension

the measures are

max(Duration)

FirstSortedValue(ID, -aggr(max(Duration), ID, Desc))

FirstSortedValue(Status, -aggr(max(Duration), ID, Desc))

FirstSortedValue(Type, -aggr(max(Duration), ID, Desc))

this is the result

• ###### Re: Selecting Top (Max) Duration For Every Status

Thanks! but I am not counting, I am trying to get all information related with the maximum duration for every status I have. Putting Status only gives the right result; however, when I add Desc for example the gets everything.

• ###### Re: Selecting Top (Max) Duration For Every Status

You need to understand the difference between dimensions and measures:

Dimensions and Measures

In your case, you want to group by status ('I want to see information per status, max duration, the Id of that max duration per status etc.')

If you want to see more information per status, don't touch your dimensions, add another expression, e.g. to see the desc for the id with the max duration per status:

FirstSortedValue(Desc, -aggr(max(Duration), ID, Desc))

• ###### Re: Selecting Top (Max) Duration For Every Status

OK...

What about if I want to present this on a chart?

Also can I make use of the function Rank?

For example:

If(Aggr(Rank(Aggr(max(Duration), Status, ID), 3, 1), Status, ID) = 1,

(Duration)

Thanks !