inlunarweektodate - script and chart function

This function finds if timestamp lies inside the part of the lunar week up to and including the last millisecond of base_date. Lunar weeks in Qlik Sense 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.
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.

Example

Result

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.

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.

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.

Add the example script to your app and run it. Then add, at least, 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 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)

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here