Get the maximum value and counts

Anonymous — Thu, 02 Nov 2017 20:54:00 GMT

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 !

topic Get the maximum value and counts in App Development

Get the maximum value and counts