Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chrisgoudy
New Contributor II

Calendar Fields are missing values

My Calendar Fields are Missing values

My year list box shows:

2450

Week Listbox is missing

1

2

and above 45

Here is my script:

//-------------------------------------------------------------

//Get Dates from Data

//-------------------------------------------------------------

DateTemp:

LOAD YearMonth

RESIDENT Transactions

ORDER BY YearMonth Asc;

LET vMinDate = Num(Peek('YearMonth', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

LET vMaxDate = Num(Peek('YearMonth', -1, 'DateTemp'));    

LET vToday = Num(today());

//-------------------------------------------------------------

//Temporary Calendar

//-------------------------------------------------------------

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num,

Date($(vMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

//-------------------------------------------------------------

//Master Calendar

//-------------------------------------------------------------

MasterCalendar:

LEFT KEEP (Transactions)

LOAD TempDate AS %_DateKey,

     TempDate AS %_DateKeyCA,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

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

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

     Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar 

ORDER BY TempDate Asc;

Drop table TempCalendar;

Drop table DateTemp;

1 Reply
chauhans85
Esteemed Contributor

Calendar Fields are missing values

set mean and max value according to you and use below code

set vMaxdate=date(today());

set vMindate=Date(AddMonths(today(),-15));

tempCal:

Load

$(vMindate)+iterno()-1 as TempDate ,

date($(vMindate)+iterno()-1) as Date

autogenerate 1  while $(vMindate)+iterno()-1<=$(vMaxdate);

LOAD TempDate AS %_DateKey,

     TempDate AS %_DateKeyCA,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

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

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

     Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar

ORDER BY TempDate Asc;

hope this helps

hope this helps

Community Browser