topic Re: MonthEnd date of Every month using For loop? in QlikView

MonthEnd date of Every month using For loop?

prajapatiamar38 — Wed, 23 Aug 2017 04:47:41 GMT

Hi Friends,

I need monthend date of every month using for loop() for last 12 months.

if month is completed it should give me the last date of the month and if month is not completed it should give Today-1 date.

Please help..

Thanks & Regards

Amarnath Prajapati

Re: MonthEnd date of Every month using For loop?

Anil_Babu_Samineni — Wed, 23 Aug 2017 04:49:46 GMT

How about this instead of loop

If(DateField >= Min(TOTAL DateField) and DateField <= Max(TOTAL DateField), MonthEnd(DateField)) as 12ME

If(DateField >= Max(TOTAL DateField)-365 and DateField <= Max(TOTAL DateField), MonthEnd(DateField)) as 12ME

Re: MonthEnd date of Every month using For loop?

prajapatiamar38 — Wed, 23 Aug 2017 05:05:51 GMT

i want using loop only, I have used below code but it is giving me last day of this month also.

let vDateStart=date(MonthEnd(AddMonths(Today(),-11)),'YYYYMMDD');

for i=0 to 11

LET vTemp=date(MonthEnd(AddMonths(vDateStart,$(i))),'YYYYMMDD');

LOAD * Inline [

Test

$(vTemp)

];

next i;

Re: MonthEnd date of Every month using For loop?

Anil_Babu_Samineni — Wed, 23 Aug 2017 05:18:14 GMT

How about this?

LET vStartDate = AddYears(Date(DateField),-1);

LET vEndDate = Date(Max(DateField));

For i = vStartDate to vEndDate

LET vTemp=MonthEnd(DateField);;
Sample:

LOAD * Inline [

Test

$(vTemp)

];

STORE Sample into Path.qvd (qvd);

Drop Table Sample;

next i;

Re: MonthEnd date of Every month using For loop?

tresB — Wed, 23 Aug 2017 05:19:50 GMT

Try like:

Load

MonthEnd(Today(),-RecNo()) as Date

autogenerate 11;

load

If(Today()=MonthEnd(today()), today(), Date(today()-1)) as Date

AutoGenerate 1;

Re: MonthEnd date of Every month using For loop?

prajapatiamar38 — Wed, 23 Aug 2017 05:50:56 GMT

Yes got it thanks..

I have used your script to find the last 12 months last date as per my requirement, then i stored the same in variable using FieldValue().

Below is my script:

Load

MonthEnd(Today(),-RecNo()) as Date

autogenerate 11;

load

If(Today()=MonthEnd(today()), today(), Date(today()-1)) as Date

AutoGenerate 1;

for a=1 to 12

v1=FieldValue('Date',$(a));

LOAD * Inline

[

Test1

$(v1)

];

NEXT a;

Re: MonthEnd date of Every month using For loop?

tresB — Wed, 23 Aug 2017 05:56:17 GMT

If you need really 12 variables, I guess you have to modify your code a bit like:

for a=1 to 12

v1$(a)=FieldValue('Date',$(a));

Note: Please mark 'helpful' and 'correct' as fit and close the thread.