Please help.
SET vFiscalYearStartMonth = 7; //First month of the fiscal year(July)
LET vStartDate = Num (YearStart (Today(), -6)); //Jan-2017
LET vEndDate = Num(YearEnd(Today(), -1)); //Dec-2023
FiscalCalendar_D:
//3. Create Fiscal Quarter and Month Name`
Load
*,
dual('Q' & ceil(FiscalMonth/3), ceil(FiscalMonth/3)) As FiscalQuarter,
dual (text(SubField(Monthname(ORDERED_DATE_C), ' ',1)), FiscalMonth) as FiscalMonthName
;
//2. Crate Fiscal Month and Year
Load
*,
mod(
(month(ORDERED_DATE_C) - $(vFiscalYearStartMonth))
,12)
+ 1
as FiscalMonth
, YearName(ORDERED_DATE_C , 0, $(vFiscalYearStartMonth)) as FiscalYear_C
//, Year(Date(AddMonths(ORDERED_DATE_C,6))) as SO_FISCAL_YEAR // commented 08MAR2023
;
//1. Create fiscal calendar records
Load
date($(vStartDate) + IterNo() - 1) as ORDERED_DATE_C //ORDERED_DATE
AutoGenerate 1
while ($(vStartDate) + IterNo() -1) <= $(vEndDate)
;
This is my fiscal calendar and it's showing the correct values.
I've linked it in the fact table using ORDERED_DATE_C field.
In the Fact Table I've created two fields against ORDERED_DATE one is ORDERED_DATE and the second one is ORDERED_DATE_C.
When I display the fiscal calendar it's showing all values correct in the fields displayed like FiscalQuarter, FiscalMonthName etc.
But when I Add the field ORDER_NUMBER from Fact Table with those fields it only shows few values in the ORDER_NUMBER filed while Orders are there against all ORDERED_DATE_C.
For your reference I'm attaching the output as excel file.
Please guide.
Thanks
