Qlik Sense Start and End Functions with Example

FREE Online Courses: Elevate Your Skills, Zero Cost Attached - Enroll Now!

Earlier we discussed Qlik Sense Functions such as color, conditional, mapping functions etc. Today, we will see Qlik Sense Start and End Functions. These Start and End Functions are a type of date and time functions. 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.

So, let’s start Qlik Sense Start and End Functions tutorial.

Qlik Sense Start and End Functions with Example

Qlik Sense Start and End Functions with Example

1. What are the Qlik Sense Start and End Functions?

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.

i. YearStart

This function returns the timestamp of the first date of the year.

Syntax:

YearStart(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the start year for.

Period_no 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.

First_month_of_year 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.

Recommended Reading – Qlik Sense Null Functions

For example,

yearstart (’19/10/2018′) returns 01/01/2018.

yearstart (’19/10/2018′, -1)returns 01/01/2017.

ii. YearEnd

This function returns the timestamp of the last date to the last millisecond of the last of the year. The data type is dual.

Syntax:

YearEnd(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the end year for.

Period_no 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.

First_month_of_year 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.

For example,

yearend(’19/10/2018′) returns 31/12/2018 23:59:59.

yearend(’19/10/2018′, -1)returns 01/01/2017 23:59:59.

iii. Yearname

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.

Syntax:

YearName(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the year for.

Period_no 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.

First_month_of_year 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.

For example,

yearname (’15/10/2018′) returns 2018.

yearname (’15/10/2018′, -1) returns 2017.

yearname (’15/10/2018′, 0, 4) returns 2018-2019.

Have a look at Qlik Sense Financial Functions

iv. Quarterstart

This function returns the start date of the quarter that a given date is falling in.

Syntax:

QuarterStart(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the quarter start date for.

Period_no 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.

First_month_of_year 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.

For example,

quarterstart(’29/10/2005′) returns 01/10/2005 as the start date of the quarter in which the given date falls.

v. Quarterend

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.

You must read – Qlik Sense Integer Functions

Syntax:

QuarterEnd(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the quarter end date for.

Period_no 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.

First_month_of_year 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.

For example,

quarterstart(’29/10/2017′) returns 31/12/2017 23:59:59 as the end date of the quarter in which the given date falls.

vi. Quartername in Qlik Sense

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.

Syntax:

QuarterName(date[, period_no[, first_month_of_year]])

Where, date is the date you want to evaluate and want the quarter name for.

Period_no is the quarter offset i.e. if the value is 0 then the start of the quarter given in the date 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.

First_month_of_year 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.

For example,

quartername(’20/10/2017′) returns Oct-Dec 2017.

quartername(’20/10/2017′, -1) returns Jul-Sep 2017 as the quarter preceding the current.

quartername(’20/10/2017′, 0, 3) returns Sep-Nov 2017.

vii. Monthstart

This function returns the date of the first day of the month given in the date.

Let’s revise Qlik Sense Mathematical Functions

Syntax:

MonthStart(date[, period_no])

Where, date is the date you want to evaluate and want the start date of the month for.

Period_no 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.

For example,

monthstart(’15/10/2018′) returns 01/10/2018.

monthstart(’15/10/2018′, -1) returns 01/09/2018.

monthstart(‘15/10/2018’, 1) returns 01/11/2018.

viii. Monthend

This function returns the date of the last day evaluated to the last millisecond of the month given in the date.

Syntax:

MonthEnd(date[, period_no])

Where, date is the date you want to evaluate and want the end date of the month for.

Period_no 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.

For example,

monthstart(’15/11/2018′) returns 30/11/2018 23:59:59.

monthstart(’15/11/2018′, -1) returns 31/10/2018 23:59:59.

monthstart(‘15/11/2018’, 1) returns 31/12/2018 23:59:59.

ix. Monthname

This function returns the name of the month evaluated from the given date.

Have a look at Qlik Sense Field Functions

Syntax:

MonthName(date[, period_no])

Where, date is the date you want to evaluate and want the name of the month for.

Period_no 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.

For example,

monthstart(’15/11/2018′) returns Nov 2018.

monthstart(’15/11/2018′, -1) returns Oct 2018.

monthstart(‘15/11/2018’, 1) returns Dec 2018.

x. Monthsstart

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.

Syntax:

MonthsStart(n_months,date[,period_no[,first_month_of_year]])

Where, n_months 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.

date is the date you want to evaluate and want the months start for.

Period_no is the period offset i.e. if the value is 0 then the start of the months given in the date 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.

First_month_of_year 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.

For example,

monthsstart(2, ’10/12/2017′) returns 01/01/2018.

monthsstart(4, ’15/10/2017, -1) returns 01/05/2017.

xi. Monthsend

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.

Do you know about Formatting Functions in Qlik Sense

Syntax:

MonthsEnd(n_months,date[,period_no[,first_month_of_year]])

Where, n_months 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.

date is the date you want to evaluate and want the months end for.

Period_no is the period offset i.e. if the value is 0 then the end of the months given in the date 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.

First_month_of_year 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.

For example,

monthsstart(2, ’10/12/2017′) returns 28/02/2018.

monthsstart(4, ’15/10/2017, -1) returns 31/08/2017.

xii. Monthsname

This function returns a string containing the range of the months along with the year corresponding to the range specified in the function.

Syntax:

MonthsName(n_months,date[,period_no[,first_month_of_year]])

Where, n_months 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.

date is the date you want to evaluate and want the months range for.

Period_no is the period offset i.e. if the value is 0 then the end of the months given in the date 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.

First_month_of_year 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.

For example,

monthsname(4, ’19/10/2017′) returns ‘Sep-Dec 2017’.

monthsname(4, ’19/10/2017′, -1) returns ‘May-Aug 2017’.

xiii. Weekstart

This function returns the date of the first day of the week mentioned in the date given in the function as input.

Syntax:

WeekStart(date [, period_no[, first_week_day]])

Where, date is the date you want to evaluate and want the week start for.

Period_no is the period offset i.e. if the value is 0 then the week start of the week given in the date 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.

Let’s revise general Numeric Functions in Qlik Sense

First_week_day 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.

For example,

  • weekstart(17/11/2018) returns 12/11/2018 as the start day or Monday of the week given in the date.
  • weekstart(20/11/2018, -1) returns 12/11/18 as the start day or Monday of the preceding week.

xiv. Weekend

This function returns the date of the last day of the week evaluated to the last millisecond mentioned in the date given in the function as input.

Syntax:

WeekEnd(date [, period_no[, first_week_day]])

Where, date is the date you want to evaluate and want the week end for.

Period_no is the period offset i.e. if the value is 0 then the week end of the week given in the date 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.

First_week_day 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.

For example,

  • weekstart(14/11/2018) returns 18/11/2018 as the end day i.e. Sunday of the week given in the date.
  • weekstart(20/11/2018, -1) returns 18/11/18 as the end day i.e. Sunday of the preceding week.

xv. Weekname

This function returns the year and the number of week corresponding to the given date.

Syntax:

WeekName(date[, period_no[,first_week_day]])

Where, date is the date you want to evaluate and want the week name for.

Period_no is the period offset i.e. if the value is 0 then the week name of the week given in the date 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.

Recommended Reading – Qlik Sense Statistical Functions

First_week_day 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.

For example,

weekname(’12/01/2017′) returns 2017/02 as the year and the week number.

weekname(’12/01/2013′, -1) returns 2017/01 as the year and the week number.

xvi. Lunarweekstart

This function returns the first day of a lunar week evaluated to the first millisecond. In Qlik Sense, lunar weeks are those weeks which start from the 1st of January.

Syntax:

LunarweekStart(date[, period_no[, first_week_day]])

Where, date is the date you want to evaluate and want the lunar week start for.

Period_no is the period offset i.e. if the value is 0 then the week start of the week given in the date is returned, but, a negative value will consider a preceding lunar week and a positive value will consider the succeeding lunar week to return the date for.

First_week_day 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.

For example,

lunarweekstart(’12/01/2017′) returns 08/01/2017 as the start date of the lunar week corresponding to the given date.

xvii. Lunarweekend

This function returns the last day of a lunar week evaluated to the last millisecond. In Qlik Sense, lunar weeks are those weeks which start from the 1st of January.

Syntax:

LunarweekEnd(date[, period_no[, first_week_day]])

Where, date is the date you want to evaluate and want the lunar week end for.

Period_no is the period offset i.e. if the value is 0 then the week end of the week given in the date is returned, but, a negative value will consider a preceding lunar week and a positive value will consider the succeeding lunar week to return the date for.

First_week_day 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.

For example,

lunarweekstart(’12/01/2017′) returns 14/01/2017 23:59:59 as the end date of the lunar week corresponding to the given date

xviii. Lunarweekname

This function returns the year and the number of week corresponding to the week given in the date provided as input.

Syntax:

LunarWeekName(date [, period_no[, first_week_day]])

Where, date is the date you want to evaluate and want the lunar week name for.

Period_no is the period offset i.e. if the value is 0 then the week name of the week given in the date is returned, but, a negative value will consider a preceding lunar week and a positive value will consider the succeeding lunar week to return the date for.

First_week_day 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.

Do you know about Qlik Sense Day Numbering Functions

For example,

lunarweekstart(’28/03/2012′) returns 2012/14 as the year and number of the lunar week corresponding to the given date.

xix. Daystart

This function returns the start of the day evaluated to the first millisecond corresponding to the time and date of the timestamp at a specific time.

You must read – Qlik Sense Day Numbering F

Syntax:

DayStart(time[, [period_no[, day_start]])

Where, time is the timestamp which will be evaluated.

Period_no is an integer value which sets the offset period. If the value is 0 then then we will evaluate the time and date provided as input. But, if the value is negative, such as -1, then preceding days are taken and if the value is 1, then succeeding days are taken up for evaluation.

Day_start is a fraction value which indicates time at which you want a new day to start. By default, the value sets a 12:00 midnight.

For example,

Daystart(28/03/2017 16:45:00) returns 29/03/2017 00:00:00.

Daystart(10/12/2017 22:30:20) returns 11/12/2017 00:00:00 as the start of the day.

xx. Dayend  

This function returns the end of the day evaluated to the last millisecond corresponding to the time and date of the timestamp at a specific time.

Syntax:

DayEnd(time[, [period_no[, day_start]])

Where, time is the timestamp which will be evaluated.

Period_no is an integer value which sets the offset period. If the value is 0 then then the time and date provided as input will be evaluated. But, if the value is negative, such as -1, then preceding days are taken and if the value is 1, then succeeding days are taken up for evaluation.

Day_start is a fraction value which indicates time at which you want a new day to start. By default, the value is set a 12:00 midnight.

For example,

Dayend(28/03/2017 16:45:00) returns 29/03/2017 23:59:59.

Dayend(10/12/2017 22:30:20) returns 11/12/2017 23:59:59 as the end of the day.

xxi. Dayname

This function returns the name of the day evaluated to the first millisecond corresponding to the time and date of the timestamp at a specific time.

Let’s discuss Qlik Sense Formatting Functions

Syntax:

DayName(time[, [period_no[, day_start]])

Where, time is the timestamp which will be evaluated.

Period_no is an integer value which sets the offset period. If the value is 0 then then we will evaluate the time and date provided as input. But, if the value is negative, such as -1, then we take preceding days and if the value is 1, then we take succeeding days for evaluation.

Day_start is a fraction value which indicates time at which you want a new day to start. By default, the value is set a 12:00 midnight.

For example,

Daystart(28/03/2017 16:45:00) returns 29/03/2017.

Daystart(10/12/2017 22:30:20) returns 11/12/2017 as the name of the day.

So, this was all in Qlik Sense Start and End Functions. Hope you liked our explanation.

2. Summary 

Hence, we completed our tutorial of Qlik Sense Start and End Functions. Thus, these were all the important functions which we used in date and time related operations in the script. Learn them here and use them in the data load scripts of your Qlik Sense apps. In case of any queries related to Qlik Sense Start and End Functions, drop your comments in the comment box below.

You must see – 

Qlik Sense Time Zone Function

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

Leave a Reply

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