topic Re: Min & Max Date in QlikView

Min & Max Date

yadhunandhanan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I need to find first,second,third minimum & maximum date of property(Means PropertyID).

Previously I used this below method to find min & max date, but now i need to do this without taking resident. How to do this? Please help me to solve this

MinMaxDate:

Load

Property,

if(previous(Property)=Property and Count(Property) >2 , Min(Date)) as DateMin,

if(previous(Property)=Property and Count(Property) >2 , Min(Date,2)) as DateMax

resident PropData

Group By Property;

join(MinMaxDate)

Load

Property,

if(previous(Property)=Property and Count(Property) >2 , Min(Date,3)) as DateMin,

if(previous(Property)=Property and Count(Property) >2 , Min(Date,4)) as DateMax

resident PropData

Group By Property;

join(MinMaxDate)

Load

Property,

if(previous(Property)=Property and Count(Property) >2 , Min(Date,5)) as DateMin,

if(previous(Property)=Property and Count(Property) >2 , Min(Date,6)) as DateMax

resident PropData

Group By Property;

Thanks

Re: Min & Max Date

philipp_ebner — Thu, 21 Jul 2016 13:20:50 GMT

You could try with the peek() function

Peek ‒ QlikView

Best of luck

Re: Min & Max Date

mohamed_ahid — Thu, 21 Jul 2016 14:42:36 GMT

load your table ordering by date and use peek function as Philipp told you .

Re: Min & Max Date

Thu, 21 Jul 2016 14:48:14 GMT

You can use

peek(DateMin)

peek(DateMax)

Re: Min & Max Date

yadhunandhanan — Fri, 22 Jul 2016 06:18:37 GMT

Thanks Abdulraj

Re: Min & Max Date

yadhunandhanan — Fri, 22 Jul 2016 06:18:50 GMT

Thanks philipp

Re: Min & Max Date

yadhunandhanan — Fri, 22 Jul 2016 06:19:10 GMT

Thanks Mohamed Ahid

Re: Min & Max Date

oknotsen — Fri, 22 Jul 2016 07:16:41 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .