
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
MonthEnd date of Every month using For loop?
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like:
Load
MonthEnd(Today(),-RecNo()) as Date
autogenerate 11;
load
If(Today()=MonthEnd(today()), today(), Date(today()-1)) as Date
AutoGenerate 1;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like:
Load
MonthEnd(Today(),-RecNo()) as Date
autogenerate 11;
load
If(Today()=MonthEnd(today()), today(), Date(today()-1)) as Date
AutoGenerate 1;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
