Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Nth Day of Quarter / year (NetWorkDays) 445 Calendar

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

0 Replies