Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mzeldenthuis160
New Contributor

Only latest date (appointment) per person

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.

3 Replies
dwforest
Valued Contributor

Re: Only latest date (appointment) per person

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])

mzeldenthuis160
New Contributor

Re: Only latest date (appointment) per person

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.

omarbensalem
Esteemed Contributor

Re: Only latest date (appointment) per person

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