5 Replies Latest reply: Jul 6, 2018 8:40 AM by Andrei Belenko RSS

    Aggregation, select max date, count

    Andrei Belenko

      Hello colleagues,

      I'm newcomer in Qlik Sence and would appreciate your help.

      I have data on employees married status (bellow) at the end of each month. If someone terminate working in company, his ID is not reflected at the end of following monthes.

         

      card_idmonthmarital_status
      131/01/2015s
      231/01/2015s
      331/01/2015m
      128/02/2015s
      228/02/2015s
      131/03/2015s
      231/03/2015m

       

      I want to reflect in KPI chart number of married persons based on the last available information on each employee.

      I suppose following algorithm:

      1) Aggregate data on card_id field

      2) Select record with max date

      3) Count({$<marital_status={"m"}>} card_id)


      Could you help me to translate this ides in lines of code?

       

      Thanks in advance,

       

      Andrei