Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vishnumusani
New Contributor

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

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;

7 Replies
MVP
MVP

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

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

Change InvoiceYear to [Calendar Year]

thakkarrahul01
Contributor

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

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

balrajahlawat
Esteemed Contributor

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

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;


MVP
MVP

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

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

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;

vishnumusani
New Contributor

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

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

MVP
MVP

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

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.