MonthEnd date of Every month using For loop?

[prajapatiamar38]

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

[Anil_Babu_Samineni]

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

[prajapatiamar38]

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;

[Anil_Babu_Samineni]

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;

[tresesco · Accepted solution]

Try like:

Load

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

autogenerate 11;

load

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

AutoGenerate 1;

[prajapatiamar38]

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));

B:

LOAD * Inline

[

Test1

$(v1)

];

NEXT a;

[tresesco]

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.