Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

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

1 Solution

Accepted Solutions
MVP
MVP

Try like:

Load

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

autogenerate 11;

load

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

AutoGenerate 1;

Capture.PNG

View solution in original post

6 Replies

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator II
Creator II

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;

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Try like:

Load

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

autogenerate 11;

load

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

AutoGenerate 1;

Capture.PNG

View solution in original post

Creator II
Creator II

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;

MVP
MVP

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.