9 Replies Latest reply: Feb 2, 2016 1:01 PM by s Walter

# Question - Tutorial Master Calender

Hi,

i try this tutorial and his data: Understanding the Master Calendar (video)

I put the field "ProductID" to the worksheet. If i choose one ProductID there is a gap in the diagramm.

How can i show all month?

regards,

Fritz

• ###### Re: Question - Tutorial Master Calender

Hi Fritz,

Are you using the dates in your table to generate the master calendar. If there are any dates missing you will not be able to do it. You need to fetch the Min(date) and Max(date) and generate the calender for all the dates between the min and max dates. Doing it that way you will not have a gap as above.

Check this script:

QuartersMap:

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Let varMinDate = Num('01/01/2016');

Let varMaxDate = Num('31/12/2016');

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

[Master Calendar]:

TempDate AS [Date],

week(TempDate) As [Week],

Year(TempDate) As [Year],

Month(TempDate) As [Month],

Day(TempDate) As [Day],

YeartoDate(TempDate)*-1 as [CurYTDFlag],

YeartoDate(TempDate,-1)*-1 as [LastYTDFlag],

inyear(TempDate, Monthstart(\$(varMaxDate)),-1) as [RC12],

date(monthstart(TempDate), 'MMM-YYYY') as [MonthYear],

ApplyMap('QuartersMap', month(TempDate), Null()) as [Quarter],

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [WeekYear],

WeekDay(TempDate) as [WeekDay]

Resident TempCalendar

Order By TempDate ASC;

store [Master Calendar] into [lib://WOC Debts Extracts/master_calander.qvd];

Drop Table TempCalendar;

• ###### Re: Question - Tutorial Master Calender

Hi Sangram,

thanks...i try your script and got this error:

regards,

Fritz

• ###### Re: Question - Tutorial Master Calender

Hi Fritz,

Check if the variables varMinDate and varMaxDate have values in them while execution. As per your screen shot the value in varMaxDate is missing.

To rectify this change the date format of the variables to your system date format and give a try:

Let varMinDate = Num('01/01/2016');  -> change it your date format

Let varMaxDate = Num('31/12/2016'); -> change it your date format

Thanks,

Sangram

• ###### Re: Question - Tutorial Master Calender

Hi Sangram,

i change it to...

Let varMinDate = Num('01.01.2014');

Let varMaxDate = Num('31.12.2016');

...and got no error.

But there are still the gaps, if i choose a ProductID.

regards,

Fritz