topic Re: Year(Today()) is not giving correct value in Script Editor in QlikView

Year(Today()) is not giving correct value in Script Editor

vishnumusani — Mon, 30 Nov 2015 09:46:41 GMT

Hi All,

I am deriving a date based upon a year value by keeping month as '12' and date as '31' so that my date will appear as YYYY/12/31.

For current year i need to create date as "CurrentYear/Current Month/lastdayofpreviousmonth"

Year Date

2009 2009/12/31

2010 2010/12/31

2015 2015/10/31 (As previous month is October)

In order to achieve that i am writing below code. but Year(Today()) is always giving '1905-07-07' because of that my formula is not working, Can you please suggest.

if( (year(Today()) =[Calendar Year]),Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

Re: Year(Today()) is not giving correct value in Script Editor

MK_QSL — Mon, 30 Nov 2015 09:53:37 GMT

IF(Year(Today()) = InvoiceYear, Date(MonthStart(Today())-1,'DD/MM/YYYY'), Date(MakeDate(InvoiceYear,12,31),'DD/MM/YYYY'))

Change InvoiceYear to [Calendar Year]

Re: Year(Today()) is not giving correct value in Script Editor

thakkarrahul01 — Mon, 30 Nov 2015 09:58:16 GMT

Hi Vishnu,

Can you try specifying format for today :

date(today(),'YYYY/MM/DD')

I think today function is giving you default format the way you have set at start while loading the model. Please tell me if it helps.

Kind Regards,

Rahul

Re: Year(Today()) is not giving correct value in Script Editor

Anonymous — Mon, 30 Nov 2015 10:14:42 GMT

Syntax is wrong, bracket after ')' will not come here, it will come at the end before 'as'

if( (year(Today()) =[Calendar Year]),Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

Try this:

if( (year(Today()) =[Calendar Year],Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

Re: Year(Today()) is not giving correct value in Script Editor

swuehl — Mon, 30 Nov 2015 10:25:49 GMT

LOAD Year,

Date( if(Year = Year(today()), Monthstart(today())-1, makedate(Year,12,31)),'YYYY-MM-DD') asTran_Date;

LOAD * INLINE [

Year

2009

2010

2015

];

Re: Year(Today()) is not giving correct value in Script Editor

tamilarasu — Mon, 30 Nov 2015 10:32:01 GMT

Hi Vishnu,

Try,

If( Year(Today()) =[Calendar Year],

Date(MonthStart(Today())-1),'YYYY-MM-DD'),

Date(MakeDate([Calendar Year],'12','31'),'YYYY-MM-DD') ) As Tran_Date;

Re: Year(Today()) is not giving correct value in Script Editor

vishnumusani — Mon, 30 Nov 2015 10:54:44 GMT

HI All,

Thanks for your response.

First of all Year(Today()) is not giving 2015, it is giving "1905-07-07" . that's the reason all our formulas won't work.

Hope you got the issue i am facing.

Regards,

Vishnu

Re: Year(Today()) is not giving correct value in Script Editor

swuehl — Mon, 30 Nov 2015 16:38:35 GMT

Year(Today()) is returning 2015. You can easily check this in a text box

=Year(Today() )

Only if you format that number (i.e. 2015) as Date, i,e,

=Date(Year(Today() ) )

then you will get something like 1905-07-07, which has a numeric representation of 2015.