topic Re: auto generate month year in QlikView

auto generate month year

Wed, 04 Apr 2012 09:21:52 GMT

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.

auto generate month year

SunilChauhan — Wed, 04 Apr 2012 09:48:38 GMT

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

Re: auto generate month year

Wed, 04 Apr 2012 10:15:00 GMT

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

Re: auto generate month year

jagannalla — Wed, 04 Apr 2012 10:26:47 GMT

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.

auto generate month year

SunilChauhan — Wed, 04 Apr 2012 11:43:25 GMT

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

hope this helps

Re: auto generate month year

Wed, 04 Apr 2012 16:26:28 GMT

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'

auto generate month year

SunilChauhan — Thu, 05 Apr 2012 05:50:19 GMT

in where conditin we can compare a field with variable

but may not variable to variable

hope this helps