topic Re: Max Date and Min Date from Date Format in QlikView

Max Date and Min Date from Date Format

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

Hi Everyone,

I have a date field in Number format like original date below.I have converted that number format date to DD-MM-YY Format date like below with below script.

Date(Remedy_Date,'DD-MM-YY') AS Remedy_Date,

In the text box I have taken the max date like '$(=Max([Remedy_Date]))' then I got the correct date as 09-09-17.

But while taking Min date like '$(=(Max([Remedy_Date])-1))' getting number format and wrong number also.

Please help me on Min date.

Thanks in advance

Re: Max Date and Min Date from Date Format

its_anandrjs — Wed, 13 Sep 2017 07:08:33 GMT

Then try this in text box

'$(=Date (Max([Remedy_Date])-1))'

'$(=Date (Min([Remedy_Date])-1))'

Re: Max Date and Min Date from Date Format

Anil_Babu_Samineni — Wed, 13 Sep 2017 07:09:59 GMT

When you talk about Min date it should min, Right? '$(=(Min([Remedy_Date])))'

And for your question, Because of -1 it it reverting to Number format so then you should use this

'$(=(Date(Max([Remedy_Date])-1,'DD-MM-YY')))'

Re: Max Date and Min Date from Date Format

Wed, 13 Sep 2017 07:10:44 GMT

for minimum date take

'$(=Min([Remedy_Date]))'

Re: Max Date and Min Date from Date Format

its_anandrjs — Wed, 13 Sep 2017 07:11:20 GMT

Sorry wrong suggestion it will be MIN not MAX

'$(=Date (Min([Remedy_Date])-1))'

Re: Max Date and Min Date from Date Format

mostwanted123 — Wed, 13 Sep 2017 07:17:00 GMT

Hi Bhavani,

Try this,

'$(=Date (Min([Remedy_Date])))'

Regards,

Pratik Bhor

Re: Max Date and Min Date from Date Format

bhavvibudagam — Wed, 13 Sep 2017 07:22:50 GMT

Hi Anil

Thanks for you reply. Actually I am expecting second max date. I have tried like the expression you have provided.

But I am getting 08-09-17 not 08-08-17

Re: Max Date and Min Date from Date Format

Anil_Babu_Samineni — Wed, 13 Sep 2017 07:24:57 GMT

What if you are written this? '$(=(Max([Remedy_Date], 2)))'

Re: Max Date and Min Date from Date Format

Kushal_Chawda — Wed, 13 Sep 2017 07:34:01 GMT

try below

=FirstSortedValue ([Remedy_Date],-[Remedy_Date],2)

Re: Max Date and Min Date from Date Format

its_anandrjs — Wed, 13 Sep 2017 07:42:06 GMT

In any text object how you can use Max([Remedy_Date], 2) you need to aggregate then by your fields then you get the output or you can do this in the loading script.

Fact:

Load

[Remedy_Date],

...

From Source;

MaxMinDates:

Load

[Remedy_Date],

Min([Remedy_Date]) as MaxDate,

Max([Remedy_Date]) as MaxDateOne,

Max([Remedy_Date],2) as MaxDateTwo

From Source

Group By [Remedy_Date];

And then use this fields in UI part.

Hope this helps you

Re: Max Date and Min Date from Date Format

Anonymous — Wed, 13 Sep 2017 08:20:51 GMT

To get maxdate

'$(=Max([Remedy_Date]))'

To get mindate

'$(=(Min([Remedy_Date])))'

To get Second highest date

'$(=(Max([Remedy_Date], 2)))'

Regards

Tahemas Momin

Re: Max Date and Min Date from Date Format

vinayakg — Wed, 13 Sep 2017 11:22:42 GMT

Try This,

To get Second Highest Date

=Date(Max(Remedy_Date,2),'MM/DD/YYYY')