topic Calendars in QlikView

Calendars

Anonymous — Wed, 22 Oct 2014 21:24:29 GMT

I have created a calendar in my Qlikview project after using a hard coded one. However on loading the script every thing works fine with the new calendar fields displaying on the sheets, However, when i try to filter using the new calendar fields my dashboard no longer works, but if i revert to inserting the calendar fields in the fact table everything works file. What could be going on?

Re: Calendars

MK_QSL — Wed, 22 Oct 2014 21:47:11 GMT

the datefieldname in your fact table and the datefieldname in your calendar table should be same, so that the association between the two table being created...

consider that you have fact table of sales and there is a date field named InvoiceDate

if you have created calendar table with hard coded data, the date should be renamed as InvoiceDate.

i.e.

date as InvoiceDate

Re: Calendars

Anonymous — Wed, 22 Oct 2014 21:52:06 GMT

Possible cause could be date formatting or time parts.

Ensure that both your calendar and your fact dimension are clear of time parts, e.g. by applying DATE(FLOOR(FactDate)) to your date field.

If you select a month/year does it impact your charts in any way at all?

Manish suggestion is of course also valid if you do not have a real link between your Calendar and Fact.

Kind regards

Niklas

Re: Calendars

Anonymous — Thu, 23 Oct 2014 06:16:21 GMT

Thank you all, i am new to QlikView and i have tried your recommendations, but still failing. The fact table and the calendar are linked through a date field named [Date Dispensed]. Please find herewith scripts for the fact table and calendar.

Fact table name [Main Table]

[Main Data]:

LOAD [Script Number],

//[Patient Address],

[Drug Name],

[Date Dispensed],

// Directions,

[Repeat or Original],

// Date([Date Dispensed], 'DD-MM-YYYY') as [Period],

[Doctor Name],

[Doctor Number],

QTY,

// QTY1,

[MedAid Amount],

Shortfall,

[Med Aid] as [Medical Aid],

[Supply days],

// [Drug Name1],

// Substitute,

// F17,

[Drug Code]

// Year([Date Dispensed]) as Year,

// Month([Date Dispensed]) as Month,

// Date([Date Dispensed], 'YYYYMMM') as [Period],

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

// Week([Date Dispensed]) as Week

FROM

[$(vFolderSourceData)Datawarehouse_2.xls]

(biff, embedded labels, table is Datawarehouse$);

Calendar script is as follows:

QuartersMap:

MAPPING LOAD

RowNo() as Month,

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

AUTOGENERATE (12);

Temp:

LOAD

min(Date(Floor([Date Dispensed]))) as minDate,

max(Date(Floor([Date Dispensed]))) as maxDate

Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));

LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD DISTINCT

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

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

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

MasterCalendar:

Load *,

AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],

AutoNumber(Period, 'PeriodID') as [PeriodID]

;

LOAD DISTINCT

TempDate as [Date Dispensed],

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

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;

DROP Table TempCalendar;

////--- Remove the temporary variables

LET varMinDate = Null();

LET varMaxDate = Null();

Thank for your assistance

Re: Calendars

Anonymous — Thu, 23 Oct 2014 06:16:58 GMT

Fact table name [Main Table]

[Main Data]:

LOAD [Script Number],

//[Patient Address],

[Drug Name],

[Date Dispensed],

// Directions,

[Repeat or Original],

// Date([Date Dispensed], 'DD-MM-YYYY') as [Period],

[Doctor Name],

[Doctor Number],

QTY,

// QTY1,

[MedAid Amount],

Shortfall,

[Med Aid] as [Medical Aid],

[Supply days],

// [Drug Name1],

// Substitute,

// F17,

[Drug Code]

// Year([Date Dispensed]) as Year,

// Month([Date Dispensed]) as Month,

// Date([Date Dispensed], 'YYYYMMM') as [Period],

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

// Week([Date Dispensed]) as Week

FROM

[$(vFolderSourceData)Datawarehouse_2.xls]

(biff, embedded labels, table is Datawarehouse$);

Calendar script is as follows:

QuartersMap:

MAPPING LOAD

RowNo() as Month,

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

AUTOGENERATE (12);

Temp:

LOAD

min(Date(Floor([Date Dispensed]))) as minDate,

max(Date(Floor([Date Dispensed]))) as maxDate

Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));

LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD DISTINCT

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

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

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

MasterCalendar:

Load *,

AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],

AutoNumber(Period, 'PeriodID') as [PeriodID]

;

LOAD DISTINCT

TempDate as [Date Dispensed],

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

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;

DROP Table TempCalendar;

////--- Remove the temporary variables

LET varMinDate = Null();

LET varMaxDate = Null();

Thanks for your assistance

Re: Calendars

Anonymous — Thu, 23 Oct 2014 06:47:19 GMT

My guess would still be to wrap your [Main Data].[Date Dispensed] as

Date(Floor([Date Dispensed])) as [Date Dispensed]

That would ensure that it is consistent with the minDate and maxDate that you use for building the MasterCalendar.

Kind regards

Niklas

Re: Calendars

Anonymous — Thu, 23 Oct 2014 07:33:39 GMT

Re: Re: Calendars

Anonymous — Thu, 23 Oct 2014 07:37:41 GMT

Thank you all, i have tried your recommendations but i am lost somewhere as i am new to QlikView. Here with my scripts:

FACT TABLE is [Main Data];

Re: Calendars

Anonymous — Thu, 23 Oct 2014 08:06:22 GMT

Many thanks, after my last posting of the script, i change the [Date Dispensed] field to Date(Floor([Date Dispensed])) as DateDispensed and made the appropriate changes to the calendar and it worked.

Most appreciated

Re: Re: Calendars

Anonymous — Thu, 23 Oct 2014 11:27:12 GMT

Many thanks, that solved my problem