Calendar Help

leocattqv — Thu, 04 May 2017 18:15:47 GMT

Hi All,

So I have a Calendar that should be working, the Calendar table is matched up with the main data table, but I am not getting a relationship with the fields

I have attached an example - does anyone have ideas?

Re: Calendar Help

Digvijay_Singh — Thu, 04 May 2017 18:26:15 GMT

You need to populate your period field properly in fact table, it is showing month names currently but calendar table data looks ok.

Re: Calendar Help

Anonymous — Thu, 04 May 2017 21:00:35 GMT

The fields have the same name but they don't have matching values.

In your Complaints qvd load try changing this

Month([Date Originated])as Period

date([Date Originated],'YYYYMM') as Period,

Re: Calendar Help

vishsaggi — Thu, 04 May 2017 21:35:23 GMT

Try this:

In your actual Fact table Load script where you have this field Period. Add like

Load .....,

Num(Floor(Period)) AS %Key_Date,

.......

FROM yoursource;

// Calendar Script Revamped *************************************************

MasterCalendar:

Load

Num(Floor(TempDate)) AS %Key_Date,

//Date(TempDate, 'MM/DD/YYYY') AS PeriodDate,

'W' & 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,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

WeekDay(TempDate) AS WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate,

maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Period', recno()))-1 AS mindate,

max(FieldValue('Period', recno())) AS maxdate

AUTOGENERATE FieldValueCount('Period');

topic Re: Calendar Help in QlikView

Calendar Help

Re: Calendar Help

Re: Calendar Help

Re: Calendar Help