How to Change this Master Calendar

[#1]

Hi All,

I have a QVW which will generate QVD on weekly basis.

now i need to change the master calendar to generate the QVD in Monthly basis. This is the master calendar we are using

LET tableloadStartTime=0;

SET vTotalRecordCount = 0;

Let NoOfDaysShift =0;

LET tableloadStartTime=Now(1);

if(WeekDay(today())<> 'Sat') and (WeekDay(today())<> 'Sun') Then

let NoOfDaysShift= num(WeekDay(today())*-1)-1;

ENDIF

IF (WeekDay(today())= 'Sun') Then

let NoOfDaysShift= -1;

ENDIF

let vExecDate = date(today()+$(NoOfDaysShift),'MM/DD/YYYY') ;

trace The date of execution is : $(vExecDate);

Let vToday = Today(1); // Custom Run

Let vToday_Num = num(floor(today(1)+$(NoOfDaysShift)));

According to this code QVD will generate on Every Sunday in a week.

Now i need to Change the code to load QVD on sunday in a Monthly once

EX: suppose if i run QVW on 29-4-2014 it generate the QVD based on 27-4-2014.

next if i change the let NoOfDaysShift= num(WeekDay(today())*-1)-1; code to

let NoOfDaysShift= num(WeekDay(today())*-1)+5; it will generate next sunday date which is 03-05-2014

same way i need to change the code . I need to generate the QVD on Monthly basis..

means if i run the QVW today it have to run Only first sunday of every month (i.e 6-4-2014). if i run the QVW tomarrow , also it has to run on 6-4-2014.

In the next month it have to run on 4-5-2014.

How to change the Master Calendar

[#2]

Please respond any one...I need help Immediately..

Regards

Abhinav

[#3]

Hi,

Use the below logic to load your script on every month first sunday.

Let vMonth_FirstSunday = Date(WeekEnd(Monthstart(Today())),'DD/MM/YYYY');
Let vToday = Date(Today(),'DD/MM/YYYY');

IF $(vToday) = $(vMonth_FirstSunday) Then
Test:
Load
'First' as First
AutoGenerate 1;

ELSE

Test1:

Load
'Second' as Second
AutoGenerate 1;
ENDIF

Hope this helps you..!

[#4]

thanks for the reply,

i want to modify the same master calendar to Monthly basis..can u tell me how to do that...

[#5]

Can you attach your sample app ?

[#6]

thanks for the reply,

i have attached my qvw ..pls check it.

[#7]

Use the below script instead of your Main tab script

$(Include=../../../Include/WW/IntegratedReporting/IntegratedReportingGlobal.txt);

LET tableloadStartTime=0;
SET vTotalRecordCount = 0;

LET tableloadStartTime=Now(1);

LET vExecDate = Date(WeekEnd(Monthstart(Today())),'MM/DD/YYYY');

TRACE The date of execution is : $(vExecDate);

Let vToday = Today(1); // Custom Run
Let vToday_Num = num(floor(vExecDate));

Hope this helps you..!

[vikasmahajan]

Find out min and max date from one or two tables and find attached complete master calender scripts from live software.

TEMP:

LOAD Max(MaxDate) AS MaxDate

FROM

[Source QVDs SIM\DIM_minmax_date.qvd]

(qvd);

/**********************Select Min and Max Date from 'TEMP' Table*************/

LET varMinDate = Num(MakeDate(2008,04,01)); //Num(peek('MinDate', 0, 'TEMP'));

LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

LET vToday = Num(today());

DROP TABLE TEMP;

//LET varMinDate = Num(MakeDate(2007,1,1));

//LET varMinDate = Num(MakeDate(2007,1,1));

//LET varMaxDate = Floor(YearEnd(AddMonths(Today(), 24)));

//LET vToday = Num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate)+IterNo()-1 AS Num,

Date($(varMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS [Posting Date],

  TempDate AS %Date,

     YearName(TempDate,0,4) AS FinancialYear,

  Date(Yearstart(TempDate,0,4)) AS YearStart,

  Date(Yearend(TempDate,0,4)) AS YearEnd,

  Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,

  date(MonthStart(TempDate),'MMM-YY') AS MonthYear,

  Month(TempDate) AS MonthName,

  If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,

  MonthStart(TempDate) AS MonthStart,

  MonthEnd(TempDate) AS MonthEnd,

  Year(TempDate) AS Year,

  'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,

  quarterstart(TempDate,0,4) AS QuarterStart,

  quarterend(TempDate,0,4) AS QuarterEnd,

  quartername(TempDate,0,4) AS QuarterName,

  if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,

  AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,

  NUM(TempDate) AS DateNum,

  monthname(TempDate) AS CalMonthYear,

  Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,

  num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection

     num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection

     num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS

RESIDENT TempCalendar 

ORDER BY TempDate Asc;

DROP Table TempCalendar;

Hope this helps you

Vikas

[#9]

Hi Ravi,

Thanks for the reply its working good...but to check for next month  u have not written...like earlier code if we change the noofshiftdays  we can check next week or month data by increasing

let NoOfDaysShift= num((WeekDay(MonthStart(today())))*-1)+6;

[#10]

I think when the time comes, the variable itself will shows the next months first sunday date.

Hope i am not wrong.