Fiscal Calendar Link With Order Date in Fact Table not showing right values when displaying fields from both tables

[mshoaib76]

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

[vikasmahajan]

Hi,

Hope this will help you :

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

Vikas

[mshoaib76]

Hi Vikas,

Thanks for your reply but my question is so simple I want to have a Date Dimension (Fiscal Calendar) which needs to be connected with Sales Fact table with Order Date, Right now I'm not considering many dates columns. So the question is Fiscal Calendar as a dimension is showing the right data as I've attached here an excel sheet but why it's not showing data when I include Fact Fields related to Sales. One thing to mention here I've designed a Start Schema. Attaching Screenshot here.