Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sheetal1234
Contributor II
Contributor II

Date formatting issue

Hi,

I am using YYYYMMDD format in below code and store in variable as below

vStartYear=date(monthend(AddMonths('$(vStartYear)',1)),'YYYYMMDD')



After reloading I got only 2 records. Though I am taking the data for 3 years. So I should get data for 3 year and all monthend data by using above logic. But I am not getting the same


Help!!





Thanks,

Sheetal




4 Replies
prma7799
Master III
Master III

Both variable are same here ??

vStartYear


Can you please share some sample data?

sheetal1234
Contributor II
Contributor II
Author

Yes. Both variable are same. If I take diffrent variable then loop goes into infinite.

refer my code below.

Let vStartYear=makedate(2015,1);

Let vEndYear=Today();

do while vStartYear <= vEndYear 

 

  let vStartYear =  monthend(AddMonths('$(vStartYear)',1));

 

//Code for testing

Load * Inline [

Test

$(vStartYear)

];

loop   


I am getting correct monthends for 2015 to 2018, but formatting is not getting as per my requirement, i.e(YYYYMMDD)

prma7799
Master III
Master III

Try this

SET DateFormat='YYYY-MM-DD';

Let vStartYear=makedate(2015,1);

Let vEndYear=Today();

do while vStartYear <= vEndYear

  let vStartYear =  monthend(AddMonths('$(vStartYear)',1));

//Code for testing

Load * Inline [

Test

$(vStartYear)

];

loop  

jonathandienst
Partner - Champion III
Partner - Champion III

This is should work. Date() does not remove the time portion and MonthEnd can take an offset parameter:


=Date(Floor(MonthEnd(vStartYear, 1)),'YYYYMMDD')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein