Hi All,
I am making use of 'Invoice Date' as my Dimension and Nth Day as measure (using NetWorkDays() function).
I want the Nth days to appear as 1,2,3,4..... where as I am getting Nth day as 1,1,1 and holidays as 0. can someone help me out with this to get Nth days as Number of days from the start date to end date provided in NetWorkDays.
Sample excel attached.
Below is the code for reference (To be inserted in Table).
Dimension = [Invoice Date]
Measure = NetWorkDays(
Min({$<
[Invoice Date.autoCalendar.Year]={$(=max(Year([Invoice Date.autoCalendar.Year])-1))}
>}[Invoice Date]),
Max({$<
[Invoice Date.autoCalendar.Year]={$(=max(Year([Invoice Date.autoCalendar.Year])-1))}
>}[Invoice Date]),
$(ListOfHolydays)) Script Below -
Set dataManagerTables = '','Data','Holiday List','Output ','Sheet1','Sheet5','_xlnm._FilterDatabase';
//This block renames script tables from non generated section which conflict with the names of managed tables
For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;
Unqualify *;
[Data]:
LOAD
[Invoice Date]
FROM [lib://SellingDaysFiles/Dummy data.xlsx]
(ooxml, embedded labels, table is Data);
[Holiday List]:
LOAD
[Holidays],
[Year]
FROM [lib://SellingDaysFiles/Dummy data.xlsx]
(ooxml, embedded labels, table is [Holiday List]);
[ListOfHolydays]:
load
concat(chr(39) & [Holidays] & chr(39),',') as list
Resident [Holiday List];
LET ListOfHolydays = Peek('list');
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [Invoice Date], [Holidays], [2017_Dates], [2018_Dates]
USING [autoCalendar] ;
Thanks,
Rashmi
