topic Re: Selecting Top (Max) Duration For Every Status in App Development

Selecting Top (Max) Duration For Every Status

Anonymous — Sat, 28 Jan 2017 13:00:49 GMT

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!

Re: Selecting Top (Max) Duration For Every Status

swuehl — Sat, 28 Jan 2017 13:06:12 GMT

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

Anonymous — Sat, 28 Jan 2017 13:33:00 GMT

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

Re: Selecting Top (Max) Duration For Every Status

swuehl — Sat, 28 Jan 2017 13:45:05 GMT

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

Anil_Babu_Samineni — Sat, 28 Jan 2017 15:17:11 GMT

Would you provide data which you want to know Maximum value

Re: Selecting Top (Max) Duration For Every Status

Anonymous — Sun, 29 Jan 2017 04:27:58 GMT

Here is a sample data:

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

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

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

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

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

| 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

Anonymous — Sun, 29 Jan 2017 04:28:34 GMT

@swuehl

Re: Selecting Top (Max) Duration For Every Status

maxgro — Sun, 29 Jan 2017 08:09:28 GMT

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

Anonymous — Sun, 29 Jan 2017 10:49:54 GMT

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

Re: Selecting Top (Max) Duration For Every Status

maxgro — Sun, 29 Jan 2017 11:12:04 GMT

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

Anonymous — Sun, 29 Jan 2017 11:15:40 GMT

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

swuehl — Sun, 29 Jan 2017 11:27:17 GMT

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

Anonymous — Sun, 29 Jan 2017 11:37:44 GMT

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 !