Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Maybe like this:
Create a straight table chart with dimension Status and use as expression
=Max(Duration)
Thanks Stefan! The suggested solution gives all rows sorted by Duration
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.
Would you provide data which you want to know Maximum value
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)
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))
Can you please explain more? where should I write this?
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