How to group to get the last value and use it as a dimension

[virilo_tejedor]

We are analyzing messaging traffic.

Each operation (use case) has some messages. For each operation, I'd like to get the last one MESSAGE_TYPE, but with a few exceptions (messages with errors or without importance)

OPERATION_ID	MESSAGE_ID	DATETIME	MESSAGE_TYPE	HAS_ERRORS
1	1	2018-09-13 9:00	CASE_USE_BEGINING	False
1	2	2018-09-13 9:01	STEP_1	False
1	3	2018-09-13 9:02	STEP_2	False
1	4	2018-09-13 9:03	STEP_3	False
1	5	2018-09-13 9:04	STEP_4	False
1	6	2018-09-13 9:05	CASE_USE_END	False
1	7	2018-09-13 9:06	NOT_IMPORTANT	False
1	8	2018-09-13 9:07	NOT_IMPORTANT	False
2	9	2018-09-13 9:05	CASE_USE_BEGINING	False
2	10	2018-09-13 9:06	STEP_	False
2	11	2018-09-13 9:07	STEP_2	False

So, at 9:07, operation 1 has been finished (CASE_USE_END), and operation 2 has progressed until STEP_2

I got it working in a table in this way:

// LAST_USEFULL_MESSAGE_TYPE
// =========================
=FirstSortedValue(MESSAGE_TYPE,-aggr(Max(
if(HAS_ERRORS='True',0,
    if(MESSAGE_TYPE='NOT_IMPORTANT',0,
     [MESSAGE_DATE]
    ))
),OPERATION_ID, MESSAGE_TYPE))

I'd like to do a chart bar with LAST_USEFULL_MESSAGE_TYPE and Count(OPERATION_ID).

In order it to show the amount of operations (use cases) in ther current progress status.

But, QlikSense doesn't allow me to use a chart bar with two measures.  It requieres a dimension and a measure.

Then, I'd like to calculate the LAST_USEFULL_MESSAGE_TYPE in load script.  And use it as a measure.

I tryed the next:

[MESSAGES]:
load
  ... 
;
select
   OPERATION_ID, MESSAGE_ID, DATETIME, MESSAGE_TYPE, HAS_ERRORS
  
from messages
;

join
LOAD
     OPERATION_ID,
    
     FirstSortedValue(MESSAGE_TYPE,-Max(
       if(HAS_ERRORS=True,0,
       if(MESSAGE_TYPE='NOT_IMPORTANT',0,
           [MESSAGE_DATE]
       ))
  )) as LAST_USEFULL_MESSAGE_TYPE

  Resident [MESSAGES]
  Group by OPERATION_ID;

But it throws an error due to the FirstSortedValue/Max: "Nested aggregation not allowed"

Now, I'm thinking in creating an additional column to mark/rank the LAST_USEFULL_MESSAGE in order to pick the LAST_USEFULL_MESSAGE_TYPE.
But perhaps it's too complicated and there is an easier way to have the bar chart working.

How could I show the amount of operations (use cases) in ther current progress status?

Thanks in advance.

Best regards,

Virilo