Qlik Community

Qlik Sense App Development

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

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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