Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
this my first post here in this website but i have learend alot from it, but know im facing problem with master calender as this message pop up when i tried to load the script
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min("INVOICE_DATE") as minDate,
max("INVOICE_DATE") as maxDate
Resident Collection_Dashboard;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar_Collection:
Load
TempDate AS "INVOICE_DATE",
date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as "Date",
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,
// inmonth(today(),TempDate,0)*-1 as MTD_FLAG,
// InMonthToDate( TempDate,AddYears(($(vDateToday)),-1),0)*-1 as LYMTD_FLAG,
If( TempDate - YearStart(TempDate) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,
if( Day(TempDate)= today(),
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()),1,0) ,
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0)) as IsInMTD,
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0) as IsInLYMTD,
// InMonthToDate(TempDate,$(vDateToday),-1,4)*-1 as LMTD_FLAG,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
// num(Year(TempDate) )+ num( Month(TempDate)) as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00') as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Day(TempDate),'00') as "MonthYearDay Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Week(TempDate),'00') as "MonthYearWeek Num",
num(Year(TempDate)-1 )& num( Month(TempDate),'00') & num( Day(TempDate),'00') as "LYMTD_Date_Num",
// floor( date#( date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'YYYYMMY'),'YYYYMMY')) as "MonthYear Num",
// ( date(TempDate, 'YYYYMM')) as "MonthYear Num",
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Join
load
1 as "MAx MTD",
date(max("Date"),'DD/MM/YYYY') as "MAX MTD Date",
"MonthYear Num"
Resident
MasterCalendar_Collection
where IsInMTD = 1
Group by "MonthYear Num";
Drop Table TempCalendar;
store Collection_Dashboard into [lib://Sadeem (lebara_qlik.admin)/Collection_Dashboard.qvd](qvd);
store MasterCalendar_Collection into [lib://Sadeem (lebara_qlik.admin)/MasterCalendar_Collection.qvd](qvd);
exit script;
It means that by:
$(varMinDate) + Iterno()-1 As Num
the variable $(varMinDate) has no valid value else is NULL. This might be caused from your INVOICE_DATE which might be not interpreted numeric else as a string which would affect your min/max aggregation as well as the num() wrapping of the peek().
Another cause could be that INVOICE_DATE isn't a date else a timestamp. In this case the value has a decimal-delimiter and if the delimiter is a comma the variable would interpret it as delimiter for the variable itself and not for the value and again the result would be NULL.
- Marcus