Qlik Sense In Function With Syntax and Example

Stay updated with the latest technology trends while you're on the move - Join DataFlair's Telegram Channel

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense Financial Functions. Today, we will see Qlik Sense In Function. Basically, we use the Qlik Sense In Function to look for or evaluate dates falling in a certain year, month or quarter. Usually, we use such functions in financial matters and so instead of considering a normal year, we consider a fiscal year. Fiscal years for different countries are different, some start from the month of April, some in the month of October etc.

So, let’s start Qlik Sense In Function Tutorial.

Qlik Sense In Function

Qlik Sense In Function With Syntax and Example

2. Qlik Sense In Function

Following are the Qlik Sense In Function, let’s discuss them in detail –

Have a look at Qlik Sense Trigonometric and Hyperbolic Functions

i. inyear() function

This function returns True if the timestamp contains the same year as mentioned in base_date. The data type returned by this function is Boolean.

The syntax of Qlik Sense inyear function:

InYear(timestamp,base_date,period_no[,first_month_of_year])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then a year after that mentioned in the timestamp is taken and if it is a negative value, then a year previous to the that of timestamp is evaluated.

Through the parameter first_month_of_year you can set the month from which the respective year will start.

For example,

inyear ('25/01/2017', '01/01/2017', 0)

will return True because the year in the base date is in the timestamp too.

inyear ('25/01/2017', '01/01/2018', 0)

returns False because the two dates don’t match.

inyear ('25/01/2017', '01/01/2017', -1)

returns False because -1 shows that the year in the base date should a year previous to that of the timestamp.

Do you know about Qlik Sense Mathematical Functions

inyear ('25/01/2017', '01/01/2016', -1)

returns True because the year in base date is one year previous to the timestamp’s year.

ii. inyeartodate() function

This function returns True if the year in the timestamp falls in the year given in the base date. The data type is Boolean as the results will return as True (-1) and False (0).

The syntax of Qlik Sense inyeartodate function:

InYearToDate(timestamp,base_date,period_no[,first_month_of_year])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then a year after that mentioned in the timestamp is taken and if it is a negative value, then a year previous to the that of timestamp is evaluated.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For example, in the code given below, invoice dates have been loaded and using the inyeartodate function, we want to check whether these dates fall within a fiscal year staring from 01/05/2017 and ending on 31/01/2018.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvoiceDate
28/03/2017
10/12/2017
5/2/2018
31/3/2018
19/5/2018
15/9/2018
11/12/2018
2/3/2019
14/5/2019
13/6/2019
];
InvoiceData:
LOAD *,
InYearToDate(InvDate, '31/01/2018', 0, 4) AS FiscalYear
Resident TempTable;
Drop table TempTable;

Let’s revise Qlik Sense Logical Functions

In the resultant table you will get True(-1) if the date falls in the given fiscal year and False(0) if it doesn’t.

InvoiceDateFiscalYear
28/03/20170(False)
10/12/2018-1(True)
5/02/20180(False)
31/03/20180(False)
19/05/20180(False)
15/09/20180(False)
11/12/20180(False)
2/03/20190(False)
14/05/20190(False)
13/06/20190(False)

iii. inquarter() function

This function returns True if the year in the timestamp lies in the quarter in which the year of base date falls.

The syntax of Qlik Sense inquarter function:

InQuarter(timestamp,base_date,period_no[,first_month_of_year])

Where, timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Do you know about Qlik Sense Mapping Functions

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For example, in the script shown below, we have loaded some invoice dates and want to check using the inquarter function that which dates falls in the quarter same as the base date 31/01/2018 of the fiscal year starting from May 1st 2017.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvoiceDate
28/03/2017
10/12/2017
5/2/2018
31/3/2018
19/5/2018
15/9/2018
11/12/2018
2/3/2019
14/5/2019
13/6/2019
];
InvoiceData:
LOAD *,
InYearToDate(InvDate,'31/01/2018',0,4)AS FiscalYearQuarter
Resident TempTable;
Drop table TempTable;

The table given below shows the results of the evaluation.

InvoiceDateFiscalYearQuarter
28/03/20170(False)
10/12/20180(False)
5/02/2018-1(True)
31/03/2018-1(True)
19/05/20180(False)
15/09/20180(False)
11/12/20180(False)
2/03/20190(False)
14/05/20190(False)
13/06/20190(False)

iv. inquartertodate() function

This function returns True if the year in the timestamp, falls in the quarter of the date shown in the base date.

Let’s take a tour to Qlik Sense Line Chart

The syntax of inquartertodate function:

InQuarterToDate(timestamp, base_date, period_no [, first_month_of_year])

Where, timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For instance, in the piece of code loaded below have the invoice dates which will be checked by the function for they fall into the fourth quarter (here the fiscal year starts from May 1st 2017, which makes May, June, July the first quarter) of the year or not. The base date is set  on 28/02/2018.

TempTable:
LOAD RecNo() as InvID, * Inline [
InvoiceDate
28/03/2017
10/12/2017
5/2/2018
31/3/2018
19/5/2018
15/9/2018
11/12/2018
2/3/2019
14/5/2019
13/6/2019
];
InvoiceData:
LOAD *,
InYearToDate(InvDate,'28/02/2018',0,4)AS FiscyearQuarterdate
Resident TempTable;
Drop table TempTable;

The resultant table gives only the date 5/02/2018 which fulfils the criterion.

InvoiceDateFiscyearQuarterdate
28/03/20170(False)
10/12/20180(False)
5/02/2018-1(True)
31/03/20180(False)
19/05/20180(False)
15/09/20180(False)
11/12/20180(False)
2/03/20190(False)
14/05/20190(False)
13/06/20190(False)

Have a look at Qlik Sense Mapping Functions

v. inmonth() function

This function returns True or -1, if the month in the timestamp is found same as that in the given base date.

The syntax of inmonth functin in Qlik Sense:

InMonth(timestamp,base_date,period_no[,first_month_of_year])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

The parameter first_month_of_year is optional.

For example,

inmonth (’25/01/2018′, ’01/01/2018′, 0) returns True because the months in timestamp and base date are the same.

inmonth(’25/01/2018′, ’01/04/2018′, 0) returns False because the months are different.

vi. inmonthtodate() function

This function retrurns True if the date in the timestamp lies in the month same as that of the base date.

The syntax of Qlik Sense inmonthtodate function:

InMonthToDate(timestamp, base_date, period_no)

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

For example,

  • inmonthtodate (’25/01/2013′, ’25/01/2013′, 0) returns True because in both timestamp and basedate the months and days are same.
  • inmonthtodate (’25/01/2013′, ’24/01/2013′, 0) returns False because the dates are not same in both.
  • inmonthtodate (’25/01/2013′, ’28/02/2013′, -1) returns True because the period_No is set to be -1 which evaluates the month previous to what is given in the base date. Her, 02 is given in the base date but the expression is evaluated for 01 which matches it with the timestamp value.

Do you know about Qlik Sense Exponential Functions

vii. inmonths() function

This function returns True if the month of the timestamp falls in the same month or within the same bi-month, quarter, tertial or half-year (six-months) as the base date falls in.

The syntax of inmonths function:

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

Where timestamp is the recorded date by your system.

N_months sets the month interval i.e. 1 for one month interval (inmonth()), 2 for bi-month, 3 for three month interval (inquarter()), 4 for tertial (four-month interval) and  6 for half year or six month interval.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For example,

inmonths(4, ’25/01/2018′, ’25/04/2018′, 0) returns True because the month interval is set at 4 and so the base date 25/04/2018 must lie between the time interval of four months i.e. 01/01/2018 to 30/04/2018.

viii. Inmonthstodate() function

This function returns True if the month of the timestamp falls in the same month or within the same bi-month, quarter, tertial or half-year (six-months) as the last millisecond of base date falls in.

The syntax of Qlik Sense inmonthtodate function :

Let’s revise Qlik Sense Counter Functions

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

Where timestamp is the recorded date by your system.

N_months sets the month interval i.e. 1 for one month interval (inmonth()), 2 for bi-month, 3 for three month interval (inquarter()), 4 for tertial (four month interval) and  6 for half year or six month interval.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For example,

  • inmonthstodate(4, ’25/01/2013′, ’25/04/2013′, 0) returns True because the date on timestamp falls on within the interval of four months.
  • inmonthstodate(4, ’26/04/2013′, ’25/04/2006′, 0) returns False because the years are different and no period offset has been set.

ix. Inweek() function

This function returns True if the date recorded in the timestamp falls in the same week as is given in the base date.

The syntax of Inweek function:

InWeek(timestamp, base_date, period_no[, first_week_day])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then a year after that mentioned in the timestamp is taken and if it is a negative value, then a year previous to the that of timestamp is evaluated.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

Have a look at Qlik Sense Conditional Functions

For example,

inweek (‘5/11/2018′,’9/11/2018’,0) returns True because both the dates fall in the same week.

inweek (’12/11/2018′,’1/11/2018′,0) returns False because both the dates do not fall in the same week.

x. Inweektodate() function

This function returns True if the date recorded in the timestamp falls in the same week as is given in the base date to the last millisecond of it.

The syntax of inweektodate function:

InWeek(timestamp, base_date, period_no[, first_week_day])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

For example,

inweek ('12/01/2006','12/01/2006',0)

returns True because both the dates fall in the same week.

inweek ('12/01/2006','11/11/2018',0)

returns False because both the dates do not fall in the same week.

Do you know about Qlik Sense Box Plot

xi. Inlunarweek() function

This function returns True if the week in timestamp falls in the lunar week same as the base date. Lunar weeks are the weeks which start from 1st of January as the first day of the week.

The syntax of inlunarweek function:

InLunarWeek(timestamp,base_date,period_no[,first_week_day])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

inlunarweek(’12/01/2017′,’14/01/2017′,0) returns True. Because the value of timestamp, 12/01/2013 falls in the week 08/01/2017 to 14/01/2017.

xii. Inlunarweektodate() function

This function returns True if the week in timestamp falls in the lunar week same as and upto the base date and to the last millisecond of it. Lunar weeks are the weeks which start from 1st of January as the first day of the week.

The syntax of Inlunarweektodate:

InLunarWeek(timestamp,base_date,period_no[,first_week_day])

Where timestamp is the recorded date by your system.

base_date is the date entered based on which the year in timestamp is evaluated.

Using period_no you can set an offset for the year. If its value is 0 then we can evaluate the current year and look for in the timestamp. But if it is positive then we take a year after that in the timestamp and if it is a negative value, then we evaluate a year previous to the that of timestamp.

Through the parameter first_month_of_year you can set the month from which the respective year will start. If you do not want the year to start from January then, take integer values from 2 to 12 for February to January respectively.

inlunarweek(’12/01/2017′,’14/01/2017′,0) returns True. Because the value of timestamp, 12/01/2013 falls in the week 08/01/2017 to 14/01/2017.

Let’s discuss Qlik Sense Pie Chart

xiii. Inday() function

This function returns True if the day in the timestamp lies in the base_timestamp too.

The syntax of Qlik sense Inday function:

InDay(timestamp, base_timestamp, period_no[, day_start])

Where timestamp is the time and date recorded by the system at a specific instant.

base_timestamp is the time and date you want to evaluate against the timestamp.

period_no helps you set an offset for the day which you need to evaluate. You use 0 if you want to evaluate the day given in the base_timestamp. We use a negative value (such as -1, -2) to consider the days previous to that of base_timestamp and a positive value considers the days after or succeeding those given in the base_timestamp.

From the day_start parameter, you can set the time of when a day would start as a fraction value. Like, 0.125 for 3AM etc. You do this when you don’t want to start a day at 12 midnight.

For example,

inday (’12/01/2017 12:23:00′, ’12/01/2017 00:00:00′, 0) returns True because both the dates denote the same day.

xiv. Indaytotime() function

This function returns True if the day in the timestamp lies in the day part of the base_timestamp and that too to the last millisecond of the time until which the day doesn’t change.

The syntax of indaytotime function:

InDayToTime(timestamp, base_timestamp, period_no[, day_start])

Where timestamp is the time and date recorded by the system at a specific instant.

base_timestamp is the time and date you want to evaluate against the timestamp.

period_no helps you set an offset for the day which you need to evaluate. We use 0 if we want to evaluate the day given in the base_timestamp. We use a negative value (such as -1, -2) to consider the days previous to that of base_timestamp and a positive value considers the days after or succeeding those given in the base_timestamp.

From the day_start parameter, you can set the time of when a day would start as a fraction value. Like, 0.125 for 3AM etc.  We do this when we don’t want to start a day at 12 midnight.

Let’s revise Qlik Sense Treemap Visualization

For example,

indaytotime (’12/01/2017 12:23:00′, ’12/01/2017 23:59:00′, 0) returns True because the time in both the timestamps is for the same day.

So, this was all in Qlik Sense In Function. Hope you like our explanation.

3. Conclusion

Hence, we discussed all the important Qlik Sense In Function. We use in function to search for time and date values.

Still, if you have any query regarding Qlik Sense In Function, ask in the comment tab.

See also – 

Qlik Sense Capabilities

Reference for Qlik Sense

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.