inlunarweektodate - script and chart function
This function returns true if timestamp lies inside the part of the lunar week up to and including the last millisecond of base_date. Lunar weeks in QlikView are defined by counting 1 January as the first day of the week.
Syntax:
InLunarWeekToDate (timestamp, base_date, period_no [, first_week_day])
Return data type: Boolean
Arguments:
Argument | Description |
---|---|
timestamp | The date that you want to compare with base_date. |
base_date | Date that is used to evaluate the lunar week. |
peroid_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. |
week_start | 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. |
Example 1:
inlunarweektodate('12/01/2013', '13/01/2013', 0)
Returns True. Because the value of timestamp, 12/01/2013 falls in the part of the week 08/01/2013 to 13/01/2013.
Example 2:
inlunarweektodate('12/01/2013', '11/01/2013', 0)
Returns False. Because the value of timestamp is later than the value base_date even though the two dates are in the same lunar week before 12/01/2012.
Example 3:
inlunarweektodate('12/01/2006', '05/01/2006', 1)
Returns True. Specifying a value of 1 for period_no shifts the base_date forward one week, so the value of timestamp falls in the part of the lunar week.
Example 4:
Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document to see the result.
This example checks if an invoice date falls in the part of the week shifted from the value of base_date by four weeks.
TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
InvoiceData:
LOAD *,
InLunarWeekToDate(InvDate, '07/01/2013', 4) AS InLWeek2DPlus4
Resident TempTable;
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 part of the week 5/02/2013 to 07/02/2013.
InvDate | InLWeek2DPlus4 |
---|---|
28/03/2012 | 0 (False) |
10/12/2012 | 0 (False) |
5/2/2013 | -1 (True) |
31/3/2013 | 0 (False) |
19/5/2013 | 0 (False) |
15/9/2013 | 0 (False) |
11/12/2013 | 0 (False) |
2/3/2014 | 0 (False) |
14/5/2014 | 0 (False) |
13/6/2014 | 0 (False) |
7/7/2014 | 0 (False) |
4/8/2014 | 0 (False) |