2 Replies Latest reply: Apr 2, 2018 7:27 AM by Petter Skjolden RSS

    Qlik Sense Mapping in dateranges

    Marianne van Gurp

      I have a question about mapping data with date ranges

      Example:

       

      I have a patient that takes medicines on different days. When that patient goes to the doctor the doctor has to check if a patient had medicines the past 10 days and if yes when was the last time.

       

      I have two tables

      Table 1

      Patientnumber

      Date of visit the doctor

       

      Table 2

      Patientnumber

      Date of taking medicine

          

       

         

      Table 1Visit the doctor
      Patientnumberdate_visitdate_visit_minus 10
      x02-02-201823-01-2018
      x10-02-201831-01-2018
      x23-02-201813-02-2018
      y03-02-201824-01-2018
      y15-02-201805-02-2018

       

        

      Table 2
      Patientnumber date medicine
      x31-01-2018
      x12-02-2018
      x24-02-2018
      y26-01-2018
      y03-03-2018

          

      I want to make table 3 with
      Visitdate with the last date of medicine if exists, max date and counter =1. =0
      Otherwise date is null and counter

       

          

          

      Patientnumberdate_visitdate_medicincounter
      x02-02-201831-01-20181
      x10-02-201831-1-20181
      x23-02-2018null0
      y03-02-201826-1-20181
      y15-02-2018null0

       

      How can I manage to get table3 in the editor of Qlik Sense?

        • Re: Qlik Sense Mapping in dateranges
          Petter Skjolden

          This should work for you:

           

          2018-04-01 09_19_52-_#QC 2018-04-01 Pasient Visits - My new sheet _ Sheets - Qlik Sense.png

          The expression to calculate 1 or 0 is:

           

              -(date_visit-Max(If([date medicine]<=date_visit, [date medicine]))<=10)

           

           

          To get the number of days from the last visit that the patient took their medicine:

           

              date_visit-Max(If([date medicine]<=date_visit, [date medicine]))

           

          To get the color coding you can use this expression in the Background Color Expression:

           

              If(date_visit-Max(If([date medicine]<=date_visit, [date medicine]))>10,Red(30),Green(40))

          • Re: Qlik Sense Mapping in dateranges
            Petter Skjolden

            If the suggestion answered your question please mark the answer as answered to close the thread. If not please tell us what you need to get more information on....