Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

quilldew
Contributor

Max(FirstDate) excluding last month using set analysis

Hi guys,

I've been trying to work this one out all morning, and have done a search but can't find anything I can understand that answers this question. I'm pretty sure I've asked it myself, but can't find it on my dashboard :S

I have a list of clients, and a each client has various 'FirstDate' listed against them.

I want a straight table to show the max(FirstDate) excluding all where that max(FirstDate) is within this current month. I've tried with set analysis, using variables, using MonthName() and MonthStart(), just can't get it to work.

TIA.

1 Solution

Accepted Solutions
MVP
MVP

Re: Max(FirstDate) excluding last month using set analysis

perhaps

=Max({<FirstDate < {$(=MonthStart(Today()))}>} FirstDate)

Max({<FirstDate = {"<$(=MonthStart(Today()))"}>} FirstDate)


(depends on format of FirstDate)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies

Re: Max(FirstDate) excluding last month using set analysis

Can you elaborate on this using an example?

MVP
MVP

Re: Max(FirstDate) excluding last month using set analysis

perhaps

=Max({<FirstDate < {$(=MonthStart(Today()))}>} FirstDate)

Max({<FirstDate = {"<$(=MonthStart(Today()))"}>} FirstDate)


(depends on format of FirstDate)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
quilldew
Contributor

Re: Max(FirstDate) excluding last month using set analysis

Hi Sunny,

Take:

LOAD * INLINE [

    Person, FirstDate

    1, 02/03/2018

    1, 03/03/2018

    1, 06/05/2018

    2, 03/06/2017

    2, 12/12/2017

];

I would like a straight table that shows:

1, 03/03/2018

2, 12/12/2017

So basically showing the Max 'FirstDate' for each 'Person' excluding this current month.

Re: Max(FirstDate) excluding last month using set analysis

I believe jontydkpi‌'s expression should work for you

Highlighted
quilldew
Contributor

Re: Max(FirstDate) excluding last month using set analysis

Works perfectly thank you. I'm sure I tried that. Genius here