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: 
Anonymous
Not applicable

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

Your help is really appreciated!

12 Replies
swuehl
MVP
MVP

Maybe like this:

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

=Max(Duration)

Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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.

Anil_Babu_Samineni

Would you provide data which you want to know Maximum value

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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)

Anonymous
Not applicable
Author

maxgro
MVP
MVP

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

Anonymous
Not applicable
Author

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

maxgro
MVP
MVP

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

1.png