i have fiscal calender that works but for the fiscal quarter it does not getting value. Its give all Q.But it wants to generate Q1,Q2,Q3,Q4
.I have the code like below. I dont knew where the mistake done. Please help me . I am new to qlikview
thanks in advance.
MinMax:
LOAD
Min(OrderDate) as MinDate,
Max(OrderDate) as MaxDate
Resident [Order Header];
Let vDateMin = Num(Peek('MinDate',0,'MinMax'));
//Let vDateMax = Num(MakeDate(2012, 10, 31));
Let vDateMax =Num(Peek('MaxDate',0,'MinMax'));
Let vFromDate = Num(MakeDate(2011, 10, 31)); //For Fundflow Stmt.
Let vToDate = Num(MonthEnd(Today())); //For Fundflow Stmt.
Let vFromStartDate = Num(MonthStart(AddMonths(Today(),-1))); //For GL Comparison.
Let vFromEndDate = Num(MonthEnd(AddMonths(Today(),-1))); //For GL Comparison.
Let vToStartDate = Num(MonthStart(Today())); //For GL Comparison.
Let vToEndDate = Num(Today()); //For GL Comparison.
Let vFiscalStartMonth = 4;
Let vToday=Date(Floor(Today()),'MM/DD/YYYY');
Let vCurrentYear = num(year(today()));
Let vShowFunds=0;
FiscalQuarterMap:
mapping load
rowno() as Month,
'Q' & ceil(month(addmonths(rowno() & '/1/2000',1-$(vFiscalStartMonth)))/3) as Quarter
autogenerate 12;
[Master Calendar]:
LOAD *,
Year & [Month (#)] as [Period (#)],
AutoNumber(Year & [Month (#)], 'MonthID') as MonthID,
Month & ' - ' & Year as Period,
'Q' & Ceil([Month (#)] / 3) as Quarter,
ceil(num(Interval(WeekEnd(Date)-MonthStart(Date)))/7) as MonthlyWeekNumber,
AutoNumber(Year & 'Q' & Ceil([Month (#)] / 3), 'QuarterID') as QuarterID;
LOAD
Date($(vDateMin) + IterNo() - 1, '$(DateFormat)') as Date,
Date($(vDateMin) + IterNo() - 1, 'DD-MM-YY') as [Validation Date],
Num(Floor(Date($(vDateMin) + IterNo() - 1, '$(DateFormat)'))) as OrderDate,
Day($(vDateMin) + IterNo() - 1) as [Day (#)],
WeekDay($(vDateMin) + IterNo() - 1) as WeekDay,
Year($(vDateMin) + IterNo() - 1) as Year,
Date(yearstart(($(vDateMin) + IterNo() - 1),0,$(vFiscalStartMonth)),'YYYY') as FiscalYear,
Date(yearstart(($(vDateMin) + IterNo() - 1),0,$(vFiscalStartMonth)),'YYYY') as TestFYear,
Dual(Month(($(vDateMin) + IterNo() - 1)), Mod((Month(($(vDateMin) + IterNo() - 1))-$(vFiscalStartMonth)),12)+1) as FiscalMonth,
Date(monthstart(($(vDateMin) + IterNo() - 1)),'MMM YYYY') as "MonthYr",
Month($(vDateMin) + IterNo() - 1) as Month,
ApplyMap('FiscalQuarterMap', Month(($(vDateMin) + IterNo() - 1))) as FiscalQuarter,
Num(Month($(vDateMin) + IterNo() - 1), '00') as [Month (#)]
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
DROP Table MinMax;
NoConcatenate
Master_Calendar:
LOAD *, AutoNumber(TestFYear&'-'&[Month (#)]&'-'&MonthlyWeekNumber,'Test1') as FiscalWeekNum
Resident [Master Calendar]
Order by OrderDate asc;
Drop Table [Master Calendar];
DisConnect;
Exit Script;
