issue with calendar

[vireshkolagimat]

Hi All,

I have created the master calendar and wanted to test how the selection appears.

But when i select the year as 2018, only two quarters are being associated. Why not the other two. Even the month also not showing properly.

Below is the screenshot for your reference.

Thank you.

Regards,

VK

[juleshartley]

We'll need to understand more about how you have built the calendar...

[juleshartley]

Often the master calendar uses the fact data to build out possible values... so this would imply that you only have fact data for Jan, Feb and October 2018.

[stabben23]

Do you use peek function from a fact table which is not order by Date?

[vireshkolagimat]

Hi, Please find the below script. Thank you.

Let vYear = Year(Today())-4;

Let vStartDate=Floor(MakeDate(vYear,1,1));

Let vFiltDate =Floor(MakeDate(Year(Today(1))-1,1,1));

Let vEndDate =Floor(YearEnd(Today()));

Let vDiff =vEndDate-vStartDate+1;

[Master Calendar]:

LOAD Distinct *, 'W'& Ceil(DayNumber / Ceil(MaxDayNumber/4)) as TargetWeek,

'W'& IF(DayNumber <= 7, 1,

IF(DayNumber > 7 and DayNumber <= 14, 2,

IF(DayNumber > 14 and DayNumber <= 21, 3,4))) as TargetWeek2;

LOAD DISTINCT

    //Year(Temp_Date) * 10000 + Month(Temp_Date) * 100 + Day(Temp_Date)  as [DateKey],

    Temp_Date,

    Date(Temp_Date, 'YYYYMMDD') as [DateKey],

     //Date(Temp_Date, 'YYYYMMDD') as [AgeingDateKey],

Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

DATE(Temp_Date,'DD/MM/YYYY')                    as [Date],

Year(Temp_Date)   as [Year],

Month(Temp_Date) as [Month],

Month(Temp_Date)&'-'&Date(Temp_Date, 'YY') as [MonthName],

num(Day(DATE(Temp_Date,'DD/MM/YYYY')))          as [DayNumber],

Num(Day(MonthEnd(Temp_Date))) as MaxDayNumber,

num(Month(DATE(Temp_Date,'DD/MM/YYYY')))        as [MonthNumber],

//IF(Date(Temp_Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Temp_Date)),'DD/MM/YYYY'),1,0) as MonthEndFlag,

IF(Date(Temp_Date,'DD/MM/YYYY') = Date(Floor(YearEnd(Temp_Date)),'DD/MM/YYYY'),1,0) as YTDFlag,

//Date(MonthEnd(Temp_Date),'YYYYMMDD') as MonthEndDate,

    //Week(Temp_Date) as WeekNumber,

'W-'& num(If(WeekDay(MonthStart(Temp_Date)) = 'Sunday', Div(Temp_Date-Date(MonthStart(Temp_Date)-WeekDay(MonthStart(Temp_Date))-1),7)

,Div(Temp_Date-Date(MonthStart(Temp_Date)-WeekDay(MonthStart(Temp_Date))-1),7)+1))  as WeekName,

If(WeekDay(MonthStart(Temp_Date)) = 'Sunday', Div(Temp_Date-Date(MonthStart(Temp_Date)-WeekDay(MonthStart(Temp_Date))-1),7)

,Div(Temp_Date-Date(MonthStart(Temp_Date)-WeekDay(MonthStart(Temp_Date))-1),7)+1) as WeekNumber,

WeekDay(Temp_Date) as DayName,

// IF(WeekDay(Temp_Date) like 'Fri' or WeekDay(Temp_Date) like 'Sat',0,1) as IsWeekDay,

1 as IsWeekDay,

Date(Temp_Date, 'YYYY-MM')   as [Year - Month],

'Q' & Ceil(Month(Temp_Date) / 3)   as [Quarter],

((Year(Temp_Date) - Year($(vStartDate)))*12) + Num(Month(Temp_Date)) as RollingMonth

;

LOAD DISTINCT

($(vStartDate) + IterNo() - 1)   as Temp_Date

AUTOGENERATE (1)

WHILE $(vStartDate) + IterNo() - 1 <= $(vEndDate);

LET vAsonDate = Today();

[vireshkolagimat]

No.

[juleshartley]

I can't replicate your problem - it works fine for me.

[Anonymous]

Hi Viresh,

I get the same result as Hartley: your code works perfect on my laptop!