{"id":45790,"date":"2018-12-17T10:58:57","date_gmt":"2018-12-17T05:28:57","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=45790"},"modified":"2018-12-18T14:54:29","modified_gmt":"2018-12-18T09:24:29","slug":"qlik-sense-start-and-end-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/qlik-sense-start-and-end-functions\/","title":{"rendered":"Qlik Sense Start and End Functions with Example"},"content":{"rendered":"

Earlier we discussed Qlik Sense Functions such as color, conditional, mapping functions<\/strong><\/a> etc. Today, we will see Qlik Sense Start and End Functions. These Start and End Functions are a type of date and time functions<\/strong><\/a>. We call them start and end functions because they primarily return timestamps or data related to the start or end of a particular period like a year, quarter, month, lunar week, week etc.<\/span><\/p>\n

So, let’s start Qlik Sense Start and End Functions tutorial.<\/p>\n

\"Qlik<\/a>

Qlik Sense Start and End Functions with Example<\/p><\/div>\n

1. What are the Qlik Sense Start and End Functions?<\/h2>\n

So, below we will discuss the different Start and End Functions in Qlik Sense, this contains year, week, day, quarter, month start and end functions. Also, we will see the syntax and example of each Qlik Sense Start and End Functions.<\/p>\n

i. YearStart<\/span><\/h3>\n

This function returns the timestamp of the first date of the year. <\/span><\/p>\n

Syntax:<\/strong><\/p>\n

YearStart(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the start year for. <\/span><\/p>\n
Period_no<\/span> is the year offset i.e. if the value is 0 then the start of the current year is returned, but, a negative value will consider a preceding year and a positive value will consider the succeeding year to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
Recommended Reading –<\/strong> Qlik Sense Null Functions<\/a><\/strong><\/p>\n
For example,<\/strong> <\/span><\/p>\n
yearstart (’19\/10\/2018′)<\/span> returns 01\/01\/2018.<\/span><\/p>\n
yearstart (’19\/10\/2018′, -1)<\/span>returns 01\/01\/2017.<\/span><\/p>\n
ii. YearEnd<\/span><\/h3>\n
This function returns the timestamp of the last date to the last millisecond of the last of the year. The data type is dual. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
YearEnd(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the end year for. <\/span><\/p>\n
Period_no<\/span> is the year offset i.e. if the value is 0 then the start of the current year is returned, but, a negative value will consider a preceding year and a positive value will consider the succeeding year to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
yearend(’19\/10\/2018′)<\/span> returns 31\/12\/2018 23:59:59.<\/span><\/p>\n
yearend(’19\/10\/2018′, -1)<\/span>returns 01\/01\/2017 23:59:59.<\/span><\/p>\n
iii. Yearname<\/span><\/h3>\n
This function returns the year corresponding to the first millisecond of the first date of the year as mentioned in the date given in the function. This function returns a four-digit integer and a corresponding textual representation as well. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
YearName(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the year for. <\/span><\/p>\n
Period_no<\/span> is the year offset i.e. if the value is 0 then the start of the current year is returned, but, a negative value will consider a preceding year and a positive value will consider the succeeding year to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
yearname (’15\/10\/2018′) <\/span>returns<\/span> 2018.<\/span><\/p>\n
yearname (’15\/10\/2018′, -1) <\/span>returns<\/span> 2017.<\/span><\/p>\n
yearname (’15\/10\/2018′, 0, 4) <\/span>returns<\/span> 2018-2019.<\/span><\/p>\n
Have a look at Qlik Sense Financial Functions<\/a><\/strong><\/p>\n
iv. Quarterstart<\/span><\/h3>\n
This function returns the start date of the quarter that a given date is falling in. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
QuarterStart(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the quarter start date for. <\/span><\/p>\n
Period_no<\/span> is the quarter offset i.e. if the value is 0 then the start of the current quarter is returned, but, a negative value will consider a preceding quarter and a positive value will consider the succeeding quarter to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
quarterstart(’29\/10\/2005′)<\/span> returns <\/span>01\/10\/2005<\/span> as the start date of the quarter in which the given date falls.<\/span><\/p>\n
v. Quarterend<\/span><\/h3>\n
This function returns the end date of the quarter that a given date is falling in. We evaluate the data to the last millisecond and then it’s corresponding quarter returns.<\/span><\/p>\n
You must read –<\/strong> Qlik Sense Integer Functions<\/a><\/strong><\/p>\n
Syntax:<\/strong><\/p>\n
QuarterEnd(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the quarter end date for. <\/span><\/p>\n
Period_no<\/span> is the quarter offset i.e. if the value is 0 then the start of the current quarter is returned, but, a negative value will consider a preceding quarter and a positive value will consider the succeeding quarter to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
quarterstart(’29\/10\/2017′)<\/span> returns <\/span>31\/12\/2017 23:59:59<\/span> as the end date of the quarter in which the given date falls.<\/span><\/p>\n
vi. Quartername in Qlik Sense<\/span><\/h3>\n
This function returns a dual value having the names of the months which constitutes a quarter along with the year corresponding to the date being evaluated in the function. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
QuarterName(date[, period_no[, first_month_of_year]])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the quarter name for. <\/span><\/p>\n
Period_no<\/span> is the quarter offset i.e. if the value is 0 then the start of the quarter given in the <\/span>date<\/span> is returned, but, a negative value will consider a preceding quarter and a positive value will consider the succeeding quarter to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
quartername(’20\/10\/2017′)<\/span> returns Oct-Dec 2017.<\/span><\/p>\n
quartername(’20\/10\/2017′, -1)<\/span> returns Jul-Sep 2017 as the quarter preceding the current.<\/span><\/p>\n
quartername(’20\/10\/2017′, 0, 3)<\/span> returns Sep-Nov 2017.<\/span><\/p>\n
vii. Monthstart<\/span><\/h3>\n
This function returns the date of the first day of the month given in the date.<\/span><\/p>\n
Let’s revise Qlik Sense Mathematical Functions<\/a><\/strong><\/p>\n
Syntax:<\/strong><\/p>\n
MonthStart(date[, period_no])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the start date of the month for. <\/span><\/p>\n
Period_no<\/span> is the month offset i.e. if the value is 0 then the start of the month in the given date is returned, but, a negative value will consider a preceding month and a positive value will consider the succeeding month to return the date for. <\/span><\/p>\n
For example,<\/strong><\/p>\n
monthstart(’15\/10\/2018′)<\/span> returns 01\/10\/2018.<\/span><\/p>\n
monthstart(’15\/10\/2018′, -1)<\/span> returns 01\/09\/2018.<\/span><\/p>\n
monthstart(\u201815\/10\/2018\u2019, 1)<\/span> returns 01\/11\/2018.<\/span><\/p>\n
viii. Monthend<\/span><\/h3>\n
This function returns the date of the last day evaluated to the last millisecond of the month given in the date. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
MonthEnd(date[, period_no])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the end date of the month for. <\/span><\/p>\n
Period_no<\/span> is the month offset i.e. if the value is 0 then the end of the month in the given date is returned, but, a negative value will consider a preceding month and a positive value will consider the succeeding month to return the date for. <\/span><\/p>\n
For example,<\/strong><\/p>\n
monthstart(’15\/11\/2018′)<\/span> returns 30\/11\/2018 23:59:59.<\/span><\/p>\n
monthstart(’15\/11\/2018′, -1)<\/span> returns 31\/10\/2018 23:59:59.<\/span><\/p>\n
monthstart(\u201815\/11\/2018\u2019, 1)<\/span> returns 31\/12\/2018 23:59:59.<\/span><\/p>\n
ix. Monthname<\/span><\/h3>\n
This function returns the name of the month evaluated from the given date.<\/span><\/p>\n
Have a look at Qlik Sense Field Functions<\/a><\/strong><\/p>\n
Syntax:<\/strong><\/p>\n
MonthName(date[, period_no])<\/pre>\n
Where, <\/span>date<\/span> is the date you want to evaluate and want the name of the month for. <\/span><\/p>\n
Period_no<\/span> is the month offset i.e. if the value is 0 then the name of the month in the given date is returned, but, a negative value will consider a preceding month and a positive value will consider the succeeding month to return the date for. <\/span><\/p>\n
For example,<\/strong><\/p>\n
monthstart(’15\/11\/2018′)<\/span> returns Nov 2018.<\/span><\/p>\n
monthstart(’15\/11\/2018′, -1)<\/span> returns Oct 2018.<\/span><\/p>\n
monthstart(\u201815\/11\/2018\u2019, 1)<\/span> returns Dec 2018.<\/span><\/p>\n
x. Monthsstart<\/span><\/h3>\n
This function returns the date of the first day of a month, bi-month, tri-month, quarter or half-year corresponding to the given date.<\/span><\/p>\n
Syntax:<\/strong><\/p>\n
MonthsStart(n_months,date[,period_no[,first_month_of_year]])<\/pre>\n
Where, <\/span>n_months<\/span> is the integer specifying the period of months. The integers we use are 1 (for a single month), 2(bi-month), 3(tri-month), 4 (tertial), 6(half year) and so on. <\/span><\/p>\n
date<\/span> is the date you want to evaluate and want the months start for. <\/span><\/p>\n
Period_no<\/span> is the period offset i.e. if the value is 0 then the start of the months given in the <\/span>date<\/span> is returned, but, a negative value will consider a preceding period and a positive value will consider the succeeding period to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
monthsstart(2, ’10\/12\/2017′)<\/span> returns <\/span>01\/01\/2018<\/span>.<\/span><\/p>\n
monthsstart(4, ’15\/10\/2017, -1)<\/span> returns <\/span>01\/05\/2017<\/span>.<\/span><\/p>\n
xi. Monthsend<\/span><\/h3>\n
This function returns the date of the last day evaluated to the last millisecond of a month, bi-month, tri-month, quarter or half-year corresponding to the given date.<\/span><\/p>\n
Do you know about Formatting Functions in Qlik Sense<\/a><\/strong><\/p>\n
Syntax:<\/strong><\/p>\n
MonthsEnd(n_months,date[,period_no[,first_month_of_year]])<\/pre>\n
Where, <\/span>n_months<\/span> is the integer specifying the period of months. The integers used are 1 (for a single month), 2(bi-month), 3(tri-month), 4 (tertial), 6(half year) and so on. <\/span><\/p>\n
date<\/span> is the date you want to evaluate and want the months end for. <\/span><\/p>\n
Period_no<\/span> is the period offset i.e. if the value is 0 then the end of the months given in the <\/span>date<\/span> is returned, but, a negative value will consider a preceding period and a positive value will consider the succeeding period to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
monthsstart(2, ’10\/12\/2017′)<\/span> returns <\/span>28\/02\/2018<\/span>.<\/span><\/p>\n
monthsstart(4, ’15\/10\/2017, -1)<\/span> returns <\/span>31\/08\/2017<\/span>.<\/span><\/p>\n
xii. Monthsname<\/span><\/h3>\n
This function returns a string<\/a><\/strong> containing the range of the months along with the year corresponding to the range specified in the function.<\/span><\/p>\n
Syntax:<\/strong><\/p>\n
MonthsName(n_months,date[,period_no[,first_month_of_year]])<\/pre>\n
Where, <\/span>n_months<\/span> is the integer specifying the period of months. The integers used are 1 (for a single month), 2(bi-month), 3(tri-month), 4 (tertial), 6(half year) and so on. <\/span><\/p>\n
date<\/span> is the date you want to evaluate and want the months range for. <\/span><\/p>\n
Period_no<\/span> is the period offset i.e. if the value is 0 then the end of the months given in the <\/span>date<\/span> is returned, but, a negative value will consider a preceding period and a positive value will consider the succeeding period to return the date for.<\/span><\/p>\n
First_month_of_year<\/span> is the number of month from which you wish to get your year started. We use this in case of a fiscal year which does not start from January.<\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n
monthsname(4, ’19\/10\/2017′)<\/span> returns \u2018<\/span>Sep-Dec 2017\u2019<\/span>. <\/span><\/p>\n
monthsname(4, ’19\/10\/2017′, -1)<\/span> returns \u2018<\/span>May-Aug 2017\u2019<\/span>.<\/span><\/p>\n
xiii. Weekstart<\/span><\/h3>\n
This function returns the date of the first day of the week mentioned in the date given in the function as input. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
WeekStart(date [, period_no[, first_week_day]])<\/pre>\n
Where,<\/span> date<\/span> is the date you want to evaluate and want the week start for. <\/span><\/p>\n
Period_no<\/span> is the period offset i.e. if the value is 0 then the week start of the week given in the <\/span>date<\/span> is returned, but, a negative value will consider a preceding week and a positive value will consider the succeeding week to return the date for.<\/span><\/p>\n
Let’s revise general Numeric Functions in Qlik Sense<\/a><\/strong><\/p>\n
First_week_day <\/span>sets the first day of the week. Different integers correspond to different week days like 0 for Monday, 1 for Tuesday, 2 for Wednesday, 3 for Thursday, 4 for Friday, 5 for Saturday and 6 for Sunday. <\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n