Get the maximum value and counts

[Anonymous]

Hello,

I want to thank everyone here for the help they provide. Maybe I'll be able to provide help soon.   I'm struggling with a basic expression. For the life of me I can't seem to get it right. Below is my data and the expected output.  The headers below represent KPI's. Essentially I would like to show KPI's with a count of distinct TicketID's for the TicketStatusDesc, but over the TicketID's and highest TicketActionNum. I only want to report on the distinct Ticket current status in TicketStatusDesc.

I attached a small workup of what I'm trying to achieve.

It seems that the below SA is looking at the highest TicketActionNum for the whole data set. How do I get it to take into consideration the TicketID without selecting the TicketID.

My expression and SA is below. For each KPI, I am changing the TicketStatusDesc={"TicketStatusDesc"}.

Sum(aggr(Count({<TicketStatusDesc={"New"},TicketActionNum={$(=max(TicketActionNum))}>}TicketID),TicketStatusDesc))

Tickets:

LOAD * INLINE [

    TicketID,TicketActionNum,TicketStatusDesc,TicketDepartmentName  

    100, 3,"In-Progress","Sales"

    100, 2,"Assigned","Sales"

    100, 1,"New","Sales"

    101, 1,"New","Marketing"       

    101, 2,"Closed","Marketing"

];

Expected KPI output with no selections being made.

New	Assigned	In Progress	Closed
0	0	1	1

Thank You in advance !