Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calendars

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

9 Replies
MK_QSL
MVP
MVP

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Thanks for your assistance

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Anonymous
Not applicable
Author

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];

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Many thanks, that solved my problem