auto generate month year

[#1]

Hi,

I need to create a variable, which will have the values of month year until current month year only.

Meaning, it willl be Jan 2011, Feb 2011, ..... April 2012.

i can think of hard coding the month, but that will give me consistent of 24 months. but i need only until current month.

If you have any hints, let me know...

Thanks.

[SunilChauhan]

load this code in you script

set vMaxdate=date(today());

set vMindate=Date(AddMonths(today(),-15));

tempCal:

Load

$(vMindate)+iterno()-1 as tempdate,

date($(vMindate)+iterno()-1) as Date,

Monthname($(vMindate)+iterno()-1) as MonthYear

autogenerate 1  while $(vMindate)+iterno()-1<=$(vMaxdate);

and then take monthyear and see

hope this helps

[#3]

Sunil,

In fact, i also need the day.

In my script, i do the following, the format is good. because i need it in digit.

but it's always giving me 24 months. because i hard-coded the months.

how can i restrict it stop at 20120430 ?

set vYear = YEAR(TODAY())-1,YEAR(TODAY());
for each y in $(vYear)
          set month = 1,2,3,4,5,6,7,8,9,10,11,12;
          for each m in $(month)
                              let vDate =day(DATE(MonthEnd(MakeDate($(y), $(m))),'YYYY-MM-DD'));
                              //let v_YYYYMMDD = $(y)*10000 + $(m)*100 ;
                              let v_YYYYMMDD = $(y)*10000 + $(m)*100 + $(vDate);
                              LOAD * INLINE [
                                  Year, format
                                  $(vDate), $(v_YYYYMMDD)
                              ];
          next
next

[jagannalla]

Use this code may be it helps you..

LET vDateMin = Num(MakeDate(2011,1,1)); 

LET vDateMax = Floor(MonthEnd(Today())); 

LET vDateToday = Num(Today()); 

TempCalendar: 

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber, 

Date($(vDateMin) + RowNo() - 1) AS TempDate 

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

Calendar: 

LOAD

MonthName(TempDate) as CalendarMonthAndYear

Resident TempCalendar;

for your reference i'm attaching sample file. please check and give ur feedback.

[SunilChauhan · Accepted solution]

try this

set vYear = YEAR(TODAY())-1,YEAR(TODAY());

for each y in $(vYear)

          set month = 1,2,3,4,5,6,7,8,9,10,11,12;

          for each m in $(month)

                              let vDate =day(DATE(MonthEnd(MakeDate($(y), $(m))),'YYYY-MM-DD'));

                              let v_YYYYMMDD = $(y)*10000 + $(m)*100 + $(vDate);

                            let vEnd_YYYYMMDD =year(today())*10000+Num(Month(today()))*100+Day(Monthend(today()));

                              LOAD * INLINE [

                                  Year, format

                                  $(vDate), $(v_YYYYMMDD)

                              ] where format<='$(vEnd_YYYYMMDD)';

          next

next

hope this helps

[#6]

Hi Sunil,

It's funny, the script works.

but when i put into my real script, it doesn't.

is the following possible?

(txt, utf8, embedded labels, delimiter is ',')
where $(VS_YYYYMMDD) < '$(vEnd_YYYYMMDD)' ;   

4/4/2012 6:15:26 PM: 0085      (txt, utf8, embedded labels, delimiter is ',')
4/4/2012 6:15:26 PM: 0086      where 20120531<= '20120430' 

[SunilChauhan]

in where conditin we can compare a field with variable

but may not variable to variable

hope this helps