Skip to main content

inmonthstodate - script and chart function

This function finds if a timestamp falls within the part of a period of the month, bi-month, quarter, four-month period, or half-year up to and including the last millisecond of base_date. It is also possible to find if the timestamp falls within a previous or following time period.

Syntax:  

InMonths (n_months, timestamp, base_date, period_no[, first_month_of_year ])

Return data type: Boolean

Arguments:  

InMonthsToDate arguments
Argument Description
n_months

The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (four-month period), or 6 (half year).

timestamp The date that you want to compare with base_date.
base_date Date that is used to evaluate the period.
period_no

The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date. Negative values in period_no indicate preceding periods and positive values indicate succeeding periods.

first_month_of_year

If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Examples and results:  

These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your load script. Change the format in the examples to suit your requirements.

Example 1:  

inmonthstodate(4, '25/01/2013', '25/04/2013', 0)

Returns True. Because the value of timestamp, 25/01/2013, lies within the four-month period 01/01/2013 up to the end of 25/04/2013, in which the value of base_date, 25/04/2013 lies.

Example 2:  

inmonthstodate(4, '26/04/2013', '25/04/2006', 0)

Returns False. Because 26/04/2013 is outside the same period as the previous example.

Example 3:  

inmonthstodate(4, '25/09/2005', '01/02/2006', -1)

Returns True. Because the value of period_no, -1, shifts the search period back one period of four months (the value of n-months), which makes the search period 01/09/2005 to 01/02/2006.

Example 4:  

inmonthstodate(4, '25/04/2006', '01/06/2006', 0, 3)

Returns True. Because the value of first_month_of_year is set to 3, which makes the search period 01/03/2006 to 01/06/2006 instead of 01/05/2006 to 01/06/2006.

Example 5:  

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 the invoice date in the table falls in the part of the bi-month period up to and including the base_date shifted forwards by four bi-month periods (by specifying period_no as 4).

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 *,

InMonthsToDate(2, InvDate, '15/02/2013', 4) AS InMths2DPlus4

Resident TempTable;

Drop table TempTable;

The resulting table contains the original dates and a column with the return value of the InMonths() function.

The search period is 01/09/2013 to 15/10/2013, because the value of base_date is shifted forwards eight months from the value in the function (15/02/2013).

Example 5 results
InvDate InMths2DPlus4
28/03/2012 0 (False)
10/12/2012 0 (False)
5/2/2013 0 (False)
31/3/2013 0 (False)
19/5/2013 0 (False)
15/9/2013 -1 (True)
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!

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com