8 Replies Latest reply: Nov 14, 2013 2:09 PM by Clever Anjos RSS

    Aggr: Latest status based on day selection

      Hello,

       

      I have the following data:

      Order_IDAction_IDDayStatus
      A101Open
      A112Open
      B201Open
      B212Open
      B223Open
      C301Open
      C312Closed
      C323Open
      C334Closed
      D401Open
      D412Open
      D423Closed
      E501Open
      E512Closed
      E525Open

       

      On each Order, a user can make an action which may change the Status of the Order.

      Example: On Day 5 the user made an action (Action_ID = 52) on Order 'E' which changed its status from 'Closed' to 'Open'.

       

      I want to create a bar graph with Status as a Dimension which counts the Orders based on the latest action made and depending on the Day selection.

       

      Example:

      If I select Days 1,2,3,4, the Graph should behave as follow:

      Open = 2

      • +1 for Order_ID = A and Action_ID = 11 (latest action made on order A within Day selection)
      • +1 for Order_ID = B and Action_ID = 22 (latest action made on order B within Day selection)

      Closed = 3

      • +1 for Order_ID = C and Action_ID = 33 (latest action made on order C within Day selection)
      • +1 for Order_ID = D and Action_ID = 42 (latest action made on order D within Day selection. Action 31 is not counted because not the latest action made)
      • +1 for Order_ID = E and Action_ID = 51 (latest action made on order E within Day selection. Action 52 is not counted in the "Open" because it was made on day 5 which is out of the selection)

       

      Qlikview.png

       

      So I built the below expression:

      =count(aggr(Max(Action_ID), Order_ID))
      

       

      I assume it works that way:

      1. Aggr builds a temp table which get the latest Action made on each order within the selection made on Day
      2. Count the Action_ID (hence the orders) depending on the status thanks to the graph dimension

       

      It doesn't work so I guess my assumptions above are incorrect.

       

      Somebody would have an idea?

        • Re: Aggr: Latest status based on day selection
          Tresesco B

          Not really sure about the requirement. Why C should not be counted for status 'Open' and max Action_ID=32? It seems that you are counting all Order_IDs once within scope. Then can try like:

          =Count(DISTINCT Order_ID)

           

          Else, you might have to explain in a simpler way.

            • Re: Aggr: Latest status based on day selection

              tresesco a écrit:

               

              Why C should not be counted for status 'Open' and max Action_ID=32?

              Because Action_ID = 32 is not the latest action made on Order C. Latest action is Action_ID=33 made on day 4 (part of the selection) so I should only look at this particular one.

               

              If I try to reformulate my requirement in a more functional way:

              "I want to count how many orders are Open and how many are Closed as of latest day of my selection on Day"

               

              If I select days 1 to 4 ("I want to count Orders per Status as of day 4"):

              Order A is Open as of day 4 (nothing happened since day 2)

              Order B is Open as of day 4 (nothing happened since day 3)

              Order C is Closed as of day 4 (it was closed on day 4)

              Order D is Closed as of day 4 (nothing happened since day 3)

              Order E is Closed as of day 4 (it was closed on day 4)

               

              If I select days 1 to 5 ("I want to count Orders per Status as of day 5"):

              Nothing will change for Orders A, B, C and D (no action/status change on day 5).

              Order E will be counted as Open instead of Closed because an action performed on day 5 (Action_ID=52) switched its status from Closed to Open

               

              Hope it clarifies.

                • Re: Aggr: Latest status based on day selection

                  I build a small table to verify my assumption 1: "Aggr builds a temp table which get the latest Action made on each order within the selection made on Day"

                   

                  It seems to work and properly adjust to selections on Day (If I select from Day 1 up to Day 5 I get Action_ID 55 for E):

                  Qlikview.pngQlikview2.PNG.png

                   

                   

                  Qlikview3.png

                   

                  Now I need to be able to count the orders and dispatch per Status but the expression [ =count(aggr(Max(Action_ID), Order_ID)) ] does not work: It does not take into account the graph dimension (Status) to dispatch:

                  Qlikview4.PNG.png

                   

                  Any idea?

                    • Re: Aggr: Latest status based on day selection
                      Clever Anjos

                      ""I want to count how many orders are Open and how many are Closed as of latest day of my selection on Day""

                      maybe this can run:

                      sum(if(aggr(firstsortedvalue(Status,-Day),ActionID)='Open',1,0)  // Open

                      sum(if(aggr(firstsortedvalue(Status,-Day),ActionID)='Closed',1,0)  // Closed

                        • Re: Re: Aggr: Latest status based on day selection

                          It indeed works:

                          sum(if(aggr(firstsortedvalue(Status,-Day),Order_ID)='Open',1,0))
                          sum(if(aggr(firstsortedvalue(Status,-Day),Order_ID)='Closed',1,0))
                          

                           

                          If I understand correctly instead of retrieving latest action and deduce the latest status from it, you directly get the latest status using FirstSortedValue within aggr using Day as the weight.

                           

                          Any idea why it doesn't work if I put Status as the graph dimension and keep one expression only (remove the if testing Status value) ?

                          Aggr is supposed to be dimension sensitive

                          sum(aggr(firstsortedvalue(Status,-Day),Order_ID))