I have many data sets in my dashboard
1st data table has field called - Date
2nd data table has field called - HC_Date
3rd data table has field called - CHG_Date
Below calendar script gives correct week number for 1st Dataset where field = "Date" but not for other 2 date fields (HC_Date & CHG_Date)
Note: I dont want to make common field like HC_Date as Date
LET vFM = 9; // first month of the fiscal year
LET varFiscal = Num(MakeDate(2021,9,1))-Num(MakeDate(2021,1,1)); //day difference from Normal Year and Fiscal Year
[Fisc]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual('FY'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),Num(Ceil(NUM(Month(AddMonths($1,1-vFM)))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual('FY'&(Right(YearStart($1,0,vFM),2)+1)&'-Q'&Num(Ceil(Num(Month(AddMonths($1,1-vFM)))/3)),QuarterStart($1,0,vFM)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month(AddMonths($1,0,1-vFM)))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Dual(Month($1), Month($1)) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Month($1)&'-'&(Right(Date(Floor($1)),2)), MonthStart($1,vFM)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
//(Right(YearStart($1,0,vFM),2)+1), Dual(Month($1), monthstart($1,1-vFM)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
// Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'), Dual('W'&Num(Week(Date-$(varFiscal)),00), Num(Week(Date-$(varFiscal)),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'), // Week (Date-$(varFiscal)) AS [FiscalWeek], Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');
// create derived fields for fiscal year
DERIVE FIELDS FROM FIELDS [Date], [HC_Date], [CHG_Date], [Candidate Start Date], [Hire Date] USING [Fisc] ;
Please guide
