Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
fasuhaibani
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
Not applicable

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)

fasuhaibani
Not applicable

Re: Selecting Top (Max) Duration For Every Status

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

swuehl
Not applicable

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.

loveisfail
Not applicable

Re: Selecting Top (Max) Duration For Every Status

Would you provide data which you want to know Maximum value

Life is so rich, and we need to respect to the life !!!
fasuhaibani
Not applicable

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)

fasuhaibani
Not applicable

Re: Selecting Top (Max) Duration For Every Status

maxgro
Not applicable

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

fasuhaibani
Not applicable

Re: Selecting Top (Max) Duration For Every Status

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

maxgro
Not applicable

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

1.png