Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to get YearMonth field from a timestamp

Hi I have a normal time field with date and time. i use

date(floor([Execution Date]),'MMM-YYYY') AS yearmonth

to get the yearmonth. but i still can't aggregate based on that. because I notice in the list box, they show multiple same yearmonth, so the date is still there. how to get rid of date from my yearmonth so that i can calculate based on that. see the picCapture.PNGCapture1.PNG

I guess it is a simple question... maybe it is my way of search but i have not find answers in the forum.

Thanks for the help!

Tags (1)
6 Replies
Not applicable

Re: How to get YearMonth field from a timestamp

Hi ,

Try the below in your load script:

MonthName(Execution Date) as YearMonth

Thanks,

Shekar!!

sorrakis01
Valued Contributor

Re: How to get YearMonth field from a timestamp

Hi,

Monthname(date) or

Year(date) & '-' & Month(date)

Regards

Not applicable

Re: How to get YearMonth field from a timestamp

Geez...Just so many functions even for date lol thanks man!

Partner
Partner

Re: How to get YearMonth field from a timestamp

Try this

=Dual(Text(Date([Execution Date], 'MMM-YYYY')), Num#(Text(Date([Execution Date], 'YYYYMM'))))

MVP
MVP

Re: How to get YearMonth field from a timestamp

another way: format as MMM-YYYY the start date of the month

date(floor(MonthStart([Execution Date])), 'MMM-YYYY') as yearmonth

qlikviewwizard
Honored Contributor II

Re: How to get YearMonth field from a timestamp

Hi Please use this.

SET DateFormat='MMM-YYYY';


Load  date ([Execution Date]) as [Execution Date] INLINE [

Execution Date

JAN-2015

FEB-2015

MAR-2015

];


Hope this will help you.