{"id":41772,"date":"2018-11-14T17:33:53","date_gmt":"2018-11-14T12:03:53","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=41772"},"modified":"2018-11-14T17:33:53","modified_gmt":"2018-11-14T12:03:53","slug":"qlik-sense-in-function","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/qlik-sense-in-function\/","title":{"rendered":"Qlik Sense In Function With Syntax and Example"},"content":{"rendered":"

1. Objective<\/span><\/h2>\n

In our last Qlik Sense Tutorial, we discussed Qlik Sense Financial Functions<\/strong><\/a>. 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.<\/span><\/p>\n

So, let’s start Qlik Sense In Function Tutorial.<\/p>\n

\"Qlik<\/a>

Qlik Sense In Function With Syntax and Example<\/p><\/div>\n

2. Qlik Sense In Function<\/h2>\n

Following are the Qlik\u00a0Sense In Function, let’s discuss them in detail –<\/p>\n

Have a look at Qlik Sense\u00a0Trigonometric and Hyperbolic Functions<\/a><\/strong><\/p>\n

i. inyear() function <\/span><\/h3>\n

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. <\/span><\/p>\n

The syntax of Qlik Sense inyear function:<\/strong><\/p>\n

InYear(timestamp,base_date,period_no[,first_month_of_year])<\/pre>\n
Where <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> 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. <\/span><\/p>\n
Through the parameter <\/span>first_month_of_year<\/span> you can set the month from which the respective year will start. <\/span><\/p>\n
For example,<\/span><\/p>\n
inyear ('25\/01\/2017', '01\/01\/2017', 0)<\/pre>\n
will return True because the year in the base date is in the timestamp too. <\/span><\/p>\n
inyear ('25\/01\/2017', '01\/01\/2018', 0)<\/pre>\n
returns False because the two dates don\u2019t match.<\/span><\/p>\n
inyear ('25\/01\/2017', '01\/01\/2017', -1)<\/pre>\n
returns False because -1 shows that the year in the base date should a year previous to that of the timestamp.<\/span><\/p>\n
Do you know about Qlik Sense Mathematical Functions<\/a><\/strong><\/p>\n
inyear ('25\/01\/2017', '01\/01\/2016', -1)<\/pre>\n
returns True because the year in base date is one year previous to the timestamp\u2019s year.<\/p>\n
ii. inyeartodate() function <\/span><\/h3>\n
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). <\/span><\/p>\n
The syntax of Qlik Sense inyeartodate function:<\/strong><\/p>\n
InYearToDate(timestamp,base_date,period_no[,first_month_of_year])<\/pre>\n
Where <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> 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. <\/span><\/p>\n
Through the parameter <\/span>first_month_of_year<\/span> 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. <\/span><\/p>\n
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. <\/span><\/p>\n
TempTable:\r\nLOAD RecNo() as InvID, * Inline [\r\nInvoiceDate\r\n28\/03\/2017\r\n10\/12\/2017\r\n5\/2\/2018\r\n31\/3\/2018\r\n19\/5\/2018\r\n15\/9\/2018\r\n11\/12\/2018\r\n2\/3\/2019\r\n14\/5\/2019\r\n13\/6\/2019\r\n];\r\nInvoiceData:\r\nLOAD *,\r\nInYearToDate(InvDate, '31\/01\/2018', 0, 4) AS FiscalYear\r\nResident TempTable;\r\nDrop table TempTable;<\/pre>\n
Let’s revise Qlik Sense Logical Functions<\/a><\/strong><\/p>\n
In the resultant table you will get True(-1) if the date falls in the given fiscal year and False(0) if it doesn\u2019t. <\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
InvoiceDate<\/b><\/td>\nFiscalYear<\/b><\/td>\n<\/tr>\n
28\/03\/2017<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
10\/12\/2018<\/span><\/td>\n-1(True)<\/span><\/td>\n<\/tr>\n
5\/02\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
31\/03\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
19\/05\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
15\/09\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
11\/12\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
2\/03\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
14\/05\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
13\/06\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
iii. inquarter() function <\/span><\/h3>\n
This function returns True if the year in the timestamp lies in the quarter in which the year of base date falls. <\/span><\/p>\n
The syntax of Qlik Sense inquarter function:<\/strong><\/p>\n
InQuarter(timestamp,base_date,period_no[,first_month_of_year])<\/pre>\n
Where, <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> 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.
\n<\/span><\/p>\n
Do you know about Qlik Sense Mapping Functions<\/a><\/strong><\/p>\n
Through the parameter <\/span>first_month_of_year<\/span> 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.<\/span><\/p>\n
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 1<\/span>st<\/span> 2017. <\/span><\/p>\n
TempTable:\r\nLOAD RecNo() as InvID, * Inline [\r\nInvoiceDate\r\n28\/03\/2017\r\n10\/12\/2017\r\n5\/2\/2018\r\n31\/3\/2018\r\n19\/5\/2018\r\n15\/9\/2018\r\n11\/12\/2018\r\n2\/3\/2019\r\n14\/5\/2019\r\n13\/6\/2019\r\n];\r\nInvoiceData:\r\nLOAD *,\r\nInYearToDate(InvDate,'31\/01\/2018',0,4)AS FiscalYearQuarter\r\nResident TempTable;\r\nDrop table TempTable;\r\n<\/pre>\n
The table given below shows the results of the evaluation.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
InvoiceDate<\/b><\/td>\nFiscalYearQuarter<\/b><\/td>\n<\/tr>\n
28\/03\/2017<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
10\/12\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
5\/02\/2018<\/span><\/td>\n-1(True)<\/span><\/td>\n<\/tr>\n
31\/03\/2018<\/span><\/td>\n-1(True)<\/span><\/td>\n<\/tr>\n
19\/05\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
15\/09\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
11\/12\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
2\/03\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
14\/05\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
13\/06\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
iv. inquartertodate() function <\/span><\/h3>\n
This function returns True if the year in the timestamp, falls in the quarter of the date shown in the base date.<\/span><\/p>\n
Let’s take a tour to Qlik Sense Line Chart<\/a><\/strong><\/p>\n
The syntax of inquartertodate function:<\/strong><\/p>\n
InQuarterToDate(<\/b>timestamp, base_date, period_no [, first_month_of_year]<\/span>)<\/b><\/p>\n
Where, <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> you can set an offset for the year. If its value is 0 then we can evaluate the current year and look\u00a0for 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.<\/span><\/p>\n
Through the parameter <\/span>first_month_of_year<\/span> 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. <\/span><\/p>\n
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 1<\/span>st<\/span> 2017, which makes May, June, July the first quarter) of the year or not. The base date is set \u00a0on 28\/02\/2018.<\/span><\/p>\n
TempTable:\r\nLOAD RecNo() as InvID, * Inline [\r\nInvoiceDate\r\n28\/03\/2017\r\n10\/12\/2017\r\n5\/2\/2018\r\n31\/3\/2018\r\n19\/5\/2018\r\n15\/9\/2018\r\n11\/12\/2018\r\n2\/3\/2019\r\n14\/5\/2019\r\n13\/6\/2019\r\n];\r\nInvoiceData:\r\nLOAD *,\r\nInYearToDate(InvDate,'28\/02\/2018',0,4)AS FiscyearQuarterdate\r\nResident TempTable;\r\nDrop table TempTable;<\/pre>\n
The resultant table gives only the date 5\/02\/2018 which fulfils the criterion. <\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
InvoiceDate<\/b><\/td>\nFiscyearQuarterdate<\/b><\/td>\n<\/tr>\n
28\/03\/2017<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
10\/12\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
5\/02\/2018<\/span><\/td>\n-1(True)<\/span><\/td>\n<\/tr>\n
31\/03\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
19\/05\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
15\/09\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
11\/12\/2018<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
2\/03\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
14\/05\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n
13\/06\/2019<\/span><\/td>\n0(False)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Have a look at Qlik Sense Mapping Functions<\/a><\/strong><\/p>\n
v. inmonth() function<\/span><\/h3>\n
This function returns True or -1, if the month in the timestamp is found same as that in the given base date. <\/span><\/p>\n
The syntax of inmonth functin in Qlik Sense:<\/strong><\/p>\n
InMonth(timestamp,base_date,period_no[,first_month_of_year])<\/pre>\n
Where <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> 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.<\/span><\/p>\n
The parameter <\/span>first_month_of_year<\/span> is optional.<\/span><\/p>\n
For example, <\/span><\/p>\n
inmonth (’25\/01\/2018′, ’01\/01\/2018′, 0)<\/span> returns True because the months in timestamp and base date are the same. <\/span><\/p>\n
inmonth(’25\/01\/2018′, ’01\/04\/2018′, 0)<\/span> returns False because the months are different. <\/span><\/p>\n
vi. inmonthtodate() function <\/span><\/h3>\n
This function retrurns True if the date in the timestamp lies in the month same as that of the base date. <\/span><\/p>\n
The syntax of Qlik Sense inmonthtodate function:<\/strong><\/p>\n
InMonthToDate(timestamp, base_date, period_no)<\/pre>\n
Where <\/span>timestamp<\/span> is the recorded date by your system.<\/span><\/p>\n
base_date<\/span> is the date entered based on which the year in timestamp is evaluated. <\/span><\/p>\n
Using <\/span>period_no<\/span> 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.<\/span><\/p>\n
For example, <\/span><\/p>\n