This function returns an integer representing the second when the fraction
of the expression is interpreted as a time
according to the standard number interpretation.
Syntax:
second (expression)
Return data type: integer
When to use it
The second() function is useful when you would like to compare aggregations by second. For example, the function can be used if you would like to see activity count distribution by second.
These dimensions can be created either in the load script by using the function to create a field in a Master Calendar table, or used directly in a chart as a calculated dimension.
Function examples
Example
Result
second(
'09:14:36' )
returns 36
second(
'0.5555' )
returns 55 ( Because 0.5555 = 13:19:55 )
Regional settings
Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples. For more information, see Modifying regional settings for apps and scripts.
Default regional settings in apps are based on the user profile. These regional format settings are not related to the language displayed in the Qlik Cloud user interface. Qlik Cloud will be displayed in the same language as the browser you are using.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
second
Results table
date
second
01/01/2022 10:10:22 PM
22
01/02/2022 8:35:54 AM
54
01/03/2022 5:40:49 AM
49
01/03/2022 2:21:53 PM
53
01/04/2022 6:49:38 PM
38
01/05/2022 7:04:57 PM
57
01/05/2022 7:34:46 PM
46
01/06/2022 8:49:09 AM
9
01/06/2022 11:29:38 AM
38
01/06/2022 10:58:34 PM
34
The values in the second field are created by using the second() function and passing the date as the expression in the preceding load.statement.
Example 2 – Chart object
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains the same dataset and scenario as the first example. However, in this example, the unchanged dataset is loaded into the application. The second values are calculated via a measure in a chart object.
Load the data and open a sheet. Create a new table and add this field as a dimension:date.
Create the following measure:
=second(date)
Results table
date
=second(date)
01/01/2022 10:10:22 PM
22
01/02/2022 8:35:54 AM
54
01/03/2022 5:40:49 AM
49
01/03/2022 2:21:53 PM
53
01/04/2022 6:49:38 PM
38
01/05/2022 7:04:57 PM
57
01/05/2022 7:34:46 PM
46
01/06/2022 8:49:09 AM
9
01/06/2022 11:29:38 AM
38
01/06/2022 10:58:34 PM
34
The values for second are created by using the second() function and passing the date as the expression in a measure for the chart object.
Example 3 – Scenario
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset of timestamps, which is generated to represent the traffic to a particular festival's ticket sales website. These timestamps and a corresponding id are loaded into a table called Web_Traffic.
The TimeStamp system variable M/D/YYYY h:mm:ss[.fff] TT is used.
In this scenario, there were 10000 tickets, which went on sale at 9:00 AM on May 20, 2021. One minute later, the tickets were sold out.
The user would like a chart object that shows, by second, the count of visits to the website.
Load script
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
tmpTimeStampCreator:
load
makedate(2022,05,20) as date
AutoGenerate 1;
join load
maketime(9+floor(rand()*2),0,floor(rand()*59)) as time
autogenerate 10000;
Web_Traffic:
load
recno() as id,
timestamp(date + time) as timestamp
resident tmpTimeStampCreator;
drop table tmpTimeStampCreator;
Results
Do the following:
Load the data and open a sheet. Create a new table.
Next, create a calculated dimensions using the following expression:
=second(timestamp)
Create an aggregation measure to calculate the total count of entries:
=count(id)
The results table will look similar to the table below, but with different values for the aggregation measure:
Results table
second(timestamp)
=count(id)
0
150
1
184
2
163
3
178
4
179
5
158
6
177
7
169
8
149
9
186
10
169
11
179
12
186
13
182
14
180
15
153
16
191
17
203
18
158
19
159
20
163
+ 39 more rows
Dimension
In Analytics Services:
A dimension is an entity used to categorize data in a chart. For example, the slices in a pie chart or the bars of a bar chart represent individual values in a dimension. Dimensions are often a single field with discrete values, but can also be calculated in an expression.
A dimension is a dataset in a data mart that forms part of the star schema. Dimension datasets hold the descriptive information for all related fields that are included in the fact table’s records. A few common examples of dimension datasets are Customer and Product. Since the data in a dimension dataset is often denormalized, dimension datasets have a large number of columns.
The load script is a sequence of statements that defines what data to load and how to link the different loaded tables. It can be generated with the Data manager, or with the Data load editor, where it also can be viewed and edited.
A field contains values, loaded from a data source. At a basic level, a field corresponds to a column in a table. Fields are used to create dimensions and measures in visualizations.
Charts are objects where calculations, aggregations, and groupings can be made. Graphical visualizations, such as bar charts and pie charts are common examples, but also non-graphical objects such as pivot tables are charts.
A chart consists of dimensions and measures, where the measures are calculated once per dimensional value. If the chart contains multiple dimensions, the measures are calculated once per combination of dimensional values.
A dimension is an entity used to categorize data in a chart. For example, the slices in a pie chart or the bars of a bar chart represent individual values in a dimension. Dimensions are often a single field with discrete values, but can also be calculated in an expression.
A dimension is a dataset in a data mart that forms part of the star schema. Dimension datasets hold the descriptive information for all related fields that are included in the fact table’s records. A few common examples of dimension datasets are Customer and Product. Since the data in a dimension dataset is often denormalized, dimension datasets have a large number of columns.
The term dataset is sometimes synonymous with table. It can refer to the original source table, the table after undergoing transformations, or the fact and dimension tables in a data mart.
It can also refer to a logical table, where there are several instance tables and views:
Current data
History, which holds previous versions of the table
A preceding load is a script construct that allows you to load from the following LOAD or SELECT statement without specifying that source. Preceding loads are often faster than resident loads.
A measure is a calculation base on one ore more aggregations. For example, the sum of sales is a single aggregation, while the sum of sales divided by the count of customers is a measure based on two aggregations.
Sheets are components of Qlik Sense apps. They present visualizations to app users so they can explore, analyze, and discover data. Sheets can be public or private.
An aggregation is a calculation using multiple records in the source tables. Often it is a single field aggregated with a function such as sum, count, min, max, or average. For example, the sum of sales.
