inlunarweek - script and chart function
This function finds if timestamp lies inside the lunar week containing base_date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week.
InLunarWeek (timestamp, base_date, period_no[, first_week_day])
Return data type: Boolean
|timestamp||The date that you want to compare with base_date.|
|base_date||Date that is used to evaluate the lunar week.|
|period_no||The lunar week can be offset by period_no. period_no is an integer, where the value 0 indicates the lunar week which contains base_date. Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks.|
|first_week_day||An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day.|
|inlunarweek('12/01/2013', '14/01/2013', 0)||Returns True. Because the value of timestamp, 12/01/2013 falls in the week 08/01/2013 to 14/01/2013.|
|inlunarweek('12/01/2013', '07/01/2013', 0)||Returns False. Because the base_date 07/01/2013 is in the lunar week defined as 01/01/2013 to 07/01/2013.|
|inlunarweek('12/01/2013', '14/01/2013', -1)||Returns False. Because specifying a value of period_no as -1 shifts the week to the previous week, 01/01/2013 to 07/01/2013.|
|inlunarweek('07/01/2013', '14/01/2013', -1)||Returns True. In comparison with the previous example, the timestamp is in the week after taking into account the shift backwards.|
|inlunarweek('11/01/2006', '08/01/2006', 0, 3)||Returns False. Because specifying a value for first_week_day as 3 means the start of the year is calculated from 04/01/2013, and so the value of base_date falls in the first week, and the value of timestamp falls in the week 11/01/2013 to 17/01/2013.|
Add the example script to your app and run it. Then add the fields listed in the results column to a sheet in your app to see the result.
This example checks if an invoice date falls in the week shifted from the value of base_date by four weeks.
LOAD RecNo() as InvID, * Inline [
InLunarWeek(InvDate, '11/01/2013', 4) AS InLWeekPlus4
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the inlunarweek() function.
The function returns True for the value of InvDate5/2/2013 because the value of base_date, 11/01/2013, is shifted by four weeks, and so falls in the week 5/02/2013 to 11/02/2013.