topic Re: issue with calendar in QlikView

issue with calendar

vireshkolagimat — Tue, 06 Feb 2018 13:09:53 GMT

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,

Re: issue with calendar

juleshartley — Tue, 06 Feb 2018 13:13:48 GMT

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

Re: issue with calendar

juleshartley — Tue, 06 Feb 2018 13:14:38 GMT

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.

Re: issue with calendar

stabben23 — Tue, 06 Feb 2018 14:06:41 GMT

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

Re: issue with calendar

vireshkolagimat — Tue, 06 Feb 2018 15:41:10 GMT

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

Re: issue with calendar

vireshkolagimat — Tue, 06 Feb 2018 15:41:37 GMT

No.

Re: issue with calendar

juleshartley — Tue, 06 Feb 2018 16:27:22 GMT

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

Re: issue with calendar

Anonymous — Tue, 06 Feb 2018 17:23:29 GMT

Hi Viresh,

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