3 Replies Latest reply: Nov 8, 2018 3:31 AM by Marcel Zeldenthuis RSS

    Only latest date (appointment) per person

    Marcel Zeldenthuis

      Hallo,

      I have read a lot of post on this subject, but still I can't get it to work.
      I work with Qlik Sence Cloud (and also tried it in Desktop)

       

      I have a Tabel (export from a calander app) with Names, Start Times, End Times, Appointment Type (and more not relevant for now)

      The members (Names) can have more enteries (differend dates) in the tabel and for differend or the same Type of appointments.

      For example:

      Name, Start Time, Type

      Anne, 2018-07-01, first visit

      Anne, 2018-07-03, second visit

      Anne, 2018-08-30, Progress

      Anne, 2018-10-15, Progress

      Bill, 2018-09-14, Progress

      Lisa, 2018-09-02, secoend visit

      Lisa, 2018-10-16, Progress

       

      Now I wand a vieuw per Member (Name), Only the appointment type 'Progress' and than only the latest one.

      For example:

      Name, Start Time, Type

      Anne, 2018-10-15, Progress

      Bill, 2018-09-14, Progress

      Lisa, 2018-10-16, Progress

       

      Later on I want also be able to use that Latest date. For example to filter al work-outs (from an other tabel that are after that date.

       

      I tried a lot. The most obvious and what I read in a lot of places, should be Max([Start Time]). But that does not work.

      See screenshots before and after

      I also tried some other solutions like FirstSortedValue, but only errors.

        • Re: Only latest date (appointment) per person
          David Forest

          You want to use Set Analysis to "filter" the aggregate.

          Also when adding fields to a table, if it contains an aggregate function it must be a Measure (not a Dimension)

           

          Max({<[Type]={'Progress'}>} [Start Time])

            • Re: Only latest date (appointment) per person
              Marcel Zeldenthuis

              Tnx David, I'm getting there.

              'Just' two challenges left.

              1. I have different Type's of appointments I wand to filter. I have added them, but if someone has had more appointment type I filter on I see from all the latest one. I tried with Match, and, or but no effect.

               

              Max({<[Type]={'Voortgangsgesprek','Voortgangsgesprek +','Premium intake','Maand evaluatie','Lifestyle gesprek'}>}StartTime)

               

              2. How can I use this date? I have a table with the work-out of the members. (date, number of visits) and I want to count the [number of visits] after that max-date.

            • Re: Only latest date (appointment) per person
              omar bensalem

              I'd recommand to do it in the script; but u can achieve it directly by expression:

               

              create a table:

              use as dimensions,

              Name , Type and

              =aggr( max({<Type={"$(=aggr(only({<Type={'Progress'}>}Type),Name,Type))"}>}StartTime)   ,Name,Type)

               

              Result:

              Capture.PNG

               

              You can even use the new dimension for max date by name as a filter :

              Capture.PNG