topic Re: Max (Month Year) in QlikView

Max (Month Year)

DestinedTale — Sat, 16 Nov 2024 04:13:48 GMT

Hi,

i have a field in a file 20190220080003 where i change the format of it from timestamp to MMM-YY in script.

When i want to display the months, it will show string 42826 instead of the format i want.

0~20190220080003~98853

let DateTimeStamp=Peek('DateTimeStamp', 0, 'LastLine');

let MonthYear=Date(Date#('01/'&mid($(DateTimeStamp),5,2)&'/'&Left($(DateTimeStamp),4)),'MMM-YY');

=[Month Year] = Apr 17

=max([Month Year]) = 42826

Re: Max (Month Year)

Anil_Babu_Samineni — Sat, 09 Mar 2019 08:25:00 GMT

Try this?

Date(max([Month Year]),'MMM YY')

Re: Max (Month Year)

passionate — Sat, 09 Mar 2019 12:08:30 GMT

these is number format for your date.

you can use date(max(timestamp),'MMM-YY')

Re: Max (Month Year)

rwunderlich — Sun, 10 Mar 2019 00:07:12 GMT

Others have pointed out that you will need the date() formatting function because max() returns a number. I'd also suggest that you can get rid of the complex string parsing by reading the timestamp string with the TimeStamp#() function.

let DateTimeStamp=TimeStamp#(Peek('DateTimeStamp', 0, 'LastLine'),'YYYYMMDDhhmmss');

Then you have a proper Qlik timestamp and can use functions like

let MonthYear MonthStart(DateTimeStamp);

-Rob