Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
vvvvvvizard
Partner - Specialist
Partner - Specialist

master calender , only months and years required and financial month , it keeps creating duplicates

I just want a calendar with Year Months , current calender below if i add a listbox shows eg Date 201301 201301 over and over again , i tried distinct  , i tried commenting out day , week , ect doesnt work , i think the problem is this code from below at the end

min(FieldValue('OrderDate', recno()))-1 as mindate,
max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');

 

Code begins here 

Sample_Date:
LOAD * inline [
OrderDate
201301  keep getting duplicates for Order date egkeep getting duplicates for Order date eg
201805
];

SET vFinYear_Month=10;

MasterCalendar:
LOAD * ,
'FQ' & Ceil(FiscalMonth/3) as FQuarter;
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(Date#(Year(TempDate)*100+Month(TempDate),'YYYYMM'),'MMM-YY') as Month_Year,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay ,
//////Fiscal Periods
YearName(TempDate, 0, $(vFinYear_Month)) AS FiscalYear,
Mod(Month(TempDate) - $(vFinYear_Month), 12)+1 AS FiscalMonth,
///////Flags normal calendar
if(InMonthToDate(TempDate,today(),0),1,0) as MTD,
if(InMonthToDate(TempDate,AddYears(today(),-1),0),1,0) as PYMTD,
if(InQuarterToDate(TempDate,today(),0),1,0) as QTD,
if(InQuarterToDate(TempDate,AddYears(today(),-1),0),1,0) as PYQTD,
if(InYearToDate(TempDate,today(),0),1,0) as YTD,
if(InYearToDate(TempDate,today(),-1),1,0) as PYTD,
///////Flags Financial calend
if(InYearToDate(TempDate,today(),0,$(vFinYear_Month)),1,0) as FYTD,
if(InYearToDate(TempDate,AddYears(today(),-1),0,$(vFinYear_Month)),1,0) as PFYTD,
if(InQuarterToDate(TempDate,today(),0,$(vFinYear_Month)),1,0) as FQTD,
if(InQuarterToDate(TempDate,AddYears(today(),-1),0,$(vFinYear_Month)),1,0) as PYfQTD,
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('OrderDate', recno()))-1 as mindate,
max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');

 

 

 

Labels (1)
1 Reply
vvvvvvizard
Partner - Specialist
Partner - Specialist
Author

It would be great if it can be done in a subroutine