Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sheetal1234
New 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
Honored Contributor III

Re: Date formatting issue

Both variable are same here ??

vStartYear


Can you please share some sample data?

sheetal1234
New Contributor II

Re: Date formatting issue

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
Honored Contributor III

Re: Date formatting issue

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  

MVP
MVP

Re: Date formatting issue

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
Community Browser