In the present competitive world, all organizations are showing an interest in Business Analytics by using different technologies like QlikView, QlikSense and Tableau etc. In different technologies, Year wise and Period wise analysis plays a major role for monitoring the business growth. In this blog I will explain the importance of Date Functions and how to use them, with some example for easy understanding. Based on the requirements given by clients, we can show them important analysis for the Sales Growth, Profit, Loss, Cost Efficiency etc. for different period.

Few of the main Date Functions along with Simple Examples and Screenshots:

Date Function:

Date () function is used to change the date format of date field to another format like (MM/DD/YYYY, MM.DD.YYYY, MM-DD-YYYY, JAN-DD-YYYY, DD.MM.YYYY, DD/MM/YYYY, DD-MM-YYYY, DD-JAN-YYYY) as per the client requirement. And, it is used to change the integer values records to date format.

See the Syntax and Examples below along with Screenshots.

Syntax: Date (Date Field name, ‘format you want’) as new field name.

Date (Date,’DD-MMM-YYYY’) as NewDateFormat

Example with Screen Shot:

LOAD
 Date,
 Date(Date,'DD-MMM-YYYY’) as NewDateFormat,
 Product,
 Sales,
 Time
FROM
 [C:\Users\abc\Desktop\Sample.xlsx](ooxml, embedded labels, table is Sheet1);

Date# () Function:

The date# function evaluates the expression as a date, according to the string given as format-code. If the format code is omitted, the default date format set in the operating system is used.

It is one type of ‘Interpretation’ function. This interpretation function uses the textual value of the input and converts this into number. Usually, we can use Date# function for converting “String to Number Format”.

Date# () Function can help the QlikView to interpret the date to Correct date format, as in, it will convert all the dates into numeric values.

By using the Date () Function, we can convert this number values to required date format.

Syntax: Date# (Date Field name) as new field name.

 Date # ([Multiple Date Formats]) as ConvertDate_Number

Example with Screen Shot:

LOAD
 Date,
 Date (Date,'DD/MMM/YYYY’) as NewDateFormat,
 [Multiple Date Formats],
 Date # ([Multiple Date Formats]) as ConvertDate_Number,
 Product,
 Sales,
 Time
FROM
 [C:\Users\abc\Desktop\Sample.xlsx]
 (ooxml, embedded labels, table is Sheet1);

Year, Quarter, Month and Week Conversion from the Date:

Year, Quarter, Month and Week to Date are the common analysis that I have seen in many applications….

Suppose, if we have a date field, and if we need to calculate yearly sales growth, quarterly sales growth, monthly sales growth and weekly sales growth, we need to get Year, Quarter, Month and Week, which we can get it from the ‘Date’ field itself, by following the below provided syntax.

Syntax:

              Year (Date Field) as New Field Name,

              ‘Q’& Ceil (Month (Date Field)/3) AS New Field Name,

               Month (Date Field) as New Field Name,

               Week (Date Field) as New Field Name

Example with Screen Shot:

LOAD
 Date,
 Date (Date,'DD/MMM/YYYY') as NewDateFormat,
 Year (Date) as Year,
 'Q'& Ceil (Month (Date) /3) AS Quarter,
 Month (Date) as Month,
 Week (Date) as Week,
 [Multiple Date Formats],
 Date # ( [Multiple Date Formats] ) as convert_Number,
 Product,
 Sales,
 Time
FROM
 [C:\Users\abc\Desktop\Sample.xlsx]
 (ooxml, embedded labels, table is Sheet1);

ALT () Function:

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

It is a conditional function which we can be used to test for the date formats whether, it contains a date according to specified date formats or not. If Yes, it will `give the original string and a valid number representation of a date.

For example, currently my date field has two different date formats. So, I want to show them in single format date which is mentioned in the script.

Syntax: Date (Alt (Date Field name),’date format’) as new field name.

 Date ( Alt ( [Multiple Date Formats] ),’DD/MMM/YY’) as altdate

Example with Screen Shot:

LOAD 
 Date,
 Date (Date,'DD/MMM/YYYY') as NewDateFormat,
 [Multiple Date Formats],
 DATE ( Date# ( [Multiple Date Formats] ),'DD/MM/YYYY') as ConvertDate_Number,
 Date ( Alt ( [Multiple Date Formats] ),'DD/MMM/YY') as altdate,
 Product,
 Sales,
 Time
FROM
 [C:\Users\abc\Desktop\Sample.xlsx]
 (ooxml, embedded labels, table is Sheet1);

Timestamp() Function:

Timestamp () formats an expression as a date and time value, in the timestamp format set in the system variables in the load script, or in the operating system, unless a format string is supplied.

By using this function, we can change the date format and time format to be used. For example, if we need only time format along with year or date, we can use this timestamp function.

Syntax: Timestamp# (Timestamp (Field name, ‘Format to be used’),’Existing format’)                                      as new field name.

Timestamp # (Timestamp (Time,’DD.MM.YYYY hh:mm:ss’),’mm/dd/yyyy hh:mm’) as Timestamp

Example with Screen Shot:

LOAD
 Date,
 Date ( Date,'DD/MMM/YYYY') as NewDateFormat,
 [Multiple Date Formats],
 Date # ( [Multiple Date Formats] ) as convert_Number,
 date (Alt ( [Multiple Date Formats] ),'DD/MMM/YY') as altdate,
 Product,
 Sales,
 Time,
 Timestamp # (Timestamp (Time,'DD.MM.YYYY hh:mm'),'mm/dd/yyyy hh:mm') as Timestamp,
 Timestamp ( Time, 'yyyy hh:mm:ss') as [Year & Time]
FROM
 [C:\Users\abc\Desktop\Sample.xlsx]
 (ooxml, embedded labels, table is Sheet1);

Additionally, by using timestamp function, we can separate the year and time format. See the below syntax.

Syntax: Timestamp (Fieldname, ‘year and time format’) as new field name

 Timestamp ( Time, ‘yyyy hh:mm:ss’) as [Year & Time]

By using timestamp function, we can also separate the time format. See the below syntax.

Syntax: Timestamp (Fieldname, ‘time format’) as new field name

Timestamp ( Time, ‘hh:mm:ss’) as Time

Timestamp# Function:

Timestamp#() evaluates an expression as a date and time value, in the timestamp format set in the load script or the operating system, unless a format string is supplied.

By using this timestamp# function, it will convert time and date into number format.

Syntax: Timestamp# (Field name, ‘Format to be used’) as new field name.

 Timestamp # ( Time, ‘dd/mm/yyyy hh:mm’) as Datetime#,

Example with Screen Shot:

LOAD 
 Date,
 Date ( Date,'DD/MMM/YYYY') as NewDateFormat,
 Year (Date) as Year,
 Month (Date) as Month,
 [Multiple Date Formats],
 Date # ( [Multiple Date Formats] ) as convert_Number,
 date (Alt ( [Multiple Date Formats] ),'DD/MMM/YY') as altdate,
 Product,
 Sales,
 Time,
 Timestamp # (Timestamp (Time,'DD.MM.YYYY hh:mm'),'mm/dd/yyyy hh:mm') as Timestamp,
 Timestamp ( Time, 'yyyy hh:mm:ss') as [Year & Time],
 Timestamp # ( Time, 'dd/mm/yyyy hh:mm') as Timestamp#
FROM
 [C:\Users\abc\Desktop\Sample.xlsx]
 (ooxml, embedded labels, table is Sheet1);

 

Please visit here: Advantages/Benefits of QlikView, QlikSense, Tableau and Power BI