topic Re: Stuck with Date Formats any help on this in QlikView

Stuck with Date Formats any help on this

Anonymous — Thu, 26 Mar 2015 12:35:11 GMT

Can any help on Date Function i am having date column with below format

LastReload

201501

as YYYYMM while using QlikView Chart objects to get Year,Month,Quarter how to achieve from this?

while using in Monthname(LastReload)=>Giving output as Sep 2451 actually expected is Jan 2015?

How to get MonthName(Jan,Feb,Mar....),month(1,2,3,4,5,6....),Quarter(2014Q4,2015Q1,2015Q2,2015Q3,...) all are expected from single Column LastReload

Re: Stuck with Date Formats any help on this

PrashantSangle — Thu, 26 Mar 2015 12:38:01 GMT

Hi,

USe

MonthName(Date#(LastReload,'YYYYMM')) as MonthName

Month(Date#(LastReload,'YYYYMM')) as Month

Regards

Re: Stuck with Date Formats any help on this

Thu, 26 Mar 2015 12:46:09 GMT

can you please share your data file or sum examples so that we can try to resolve this issue.

Regards

Priyanka

Re: Stuck with Date Formats any help on this

Anonymous — Thu, 26 Mar 2015 12:53:22 GMT

Max your solution helped can you help on Quarter==>Quarter(2015Q1,...)

how to write the logic for Quarter

Re: Stuck with Date Formats any help on this

Ralf-Narfeldt — Thu, 26 Mar 2015 13:25:39 GMT

Year(Date#(LastReload,'YYYYMM')) & 'Q' & Ceil(Month(Date#(LastReload,'YYYYMM'))/3) AS QuarterYear

Re: Stuck with Date Formats any help on this

Thu, 26 Mar 2015 13:36:03 GMT

Hi,

You can try this

in script editor

Let LastReload = now();

Let Quarter=if(len(LastReload)>0, 'Q' & ceil(month(LastReload)/3));

In textbox or where ever you want to display lastReload

=Year(LastReload) & Quarter

Regards,

Priyanka

Re: Stuck with Date Formats any help on this

Colin-Albert — Thu, 26 Mar 2015 14:14:37 GMT

Just use the ReloadTime function and format the value returned as a date or date time using the format expression you require in a text object.

=date(reloadtime(), 'DD MMM YYYY hh:mm')

=date(reloadtime(), 'DD/MM/YY')

='Last Reloaded ' & date(reloadtime(), 'DD/MM/YY')

Re: Stuck with Date Formats any help on this

Anonymous — Thu, 26 Mar 2015 14:58:49 GMT

year(Date#(LastReload,'YYYY')) as LatestYear, is giving 201501

but expected is 2015

Thanks for your solutions really helped a lot.

Re: Stuck with Date Formats any help on this

Thu, 26 Mar 2015 15:02:15 GMT

Hi,

You can also try

=Year(ReloadTime()) & if(len(ReloadTime())>0, 'Q' & ceil(month(ReloadTime())/3))

Regards

Priyanka

Re: Stuck with Date Formats any help on this

Colin-Albert — Thu, 26 Mar 2015 16:55:40 GMT

Date# is a command for converting text to a date - it converts the data that is input.

To format the output data , use the date() function.

Use either date(LastReload,'YYYY')

or year(LastReload)

Assuming that the LastReload field is correctly loaded as a date field!

Otherwise just use the reloadtime() function. e.g. year(reloadtime())

Re: Stuck with Date Formats any help on this

Colin-Albert — Thu, 26 Mar 2015 17:05:30 GMT

This post and the linked Technical Brief on Dates has a clear explanation of date formatting in QlikView

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right