0 Replies Latest reply: Nov 2, 2017 4:54 PM by Matt Moskaluk RSS

    Get the maximum value and counts

    Matt Moskaluk

      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.

      NewAssignedIn ProgressClosed
      0011

       

       

      Thank You in advance !