Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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
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
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];
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
Many thanks, that solved my problem