4 Replies Latest reply: Jun 19, 2018 4:30 PM by Al Sellaro RSS

    Accumulated average and set expression

    Al Sellaro

      Hi, folks.

       

      I need to implement a kind of "accumulated"/moving average. I am stuck with the set expression to determine the calculation universe I will use.

       

      I have some ticket data as the example below:

       

      RecNoSnapshot_DateTicketTicket_Age
      106/10/2018A10
      206/10/2018B5
      36/10/2016C20
      406/11/2018A10
      506/11/2018B6
      606/11/2018C21
      706/12/2018A10
      806/12/2018B7
      906/12/2018C21
      1006/12/2018D1

       

      The user must be able to select whatever Snapshot_Date she wants. Qlik should calculate the "accumulated average age" up to the selected Snapshot_Date. Some examples:


      User Snapshot_Date selection: 06/10/2018

      Result: 11.67

      Calculation: (10 + 5 + 20) / 3

      Corresponding RecNo: 1 to 3

       

      User Snapshot_Date selection: 06/11/2018

      Result: 12

      Calculation: (10 + 5 + 20 + 10 + 6 + 21) / 6

      Corresponding RecNo: 1 to 6

       

      User Snapshot_Date selection: 06/11/2018 and 06/12/2018

      Result: 10.86

      Calculation: (10 + 6 + 21 + 10 + 7 + 21 + 1) / 7

      Corresponding RecNo: 4 to 10

       

      I try using something like the calculation below, to no avail.

      Sum({$ <Snapshot_Date = {"<=$(=Max([Snapshot_Date]))"}>} Ticket_Age) / Count({$<snapshot_date = {"<=$(=Max(Snapshot_Date))"}>} Ticket)
      

       

      Any advice?