.png "Employee"){kind=icon}
Introduction
This article discusses the best practices of using a line chart to visualize data in Incorta.
A line chart can be helpful for seeing a trend over time - specifically, the trend of continuous data. The x-axis displays data by date or by time (timestamp).
What you need to know before reading this article
Let's Go
First, let's understand the difference between discrete data and continuous data.
- Discrete - a categorical type of data. It is often represented as a string or number, but they are distinct values and cannot be compared with each other by the values themselves. Example: Count of computers in each office location.
- Continuous - this type of data can be sorted and a scale can be used to measure where one data point is greater than or less than the another value. Example: The growth of stock value over a 5-year period.
A line chart can technically be used to draw categorical values on the x-axis, but they are better used for displaying continuous data over a time period.
Format date data to get continuous values
If you are showing the trend of data across a timespan, use the following functions to derive the desired granularity of continuous data; by year, by quarter, by month, by day, etc.
- removeTime() - To produce a date column from a DateTime, the removeTime() function can be used. Time data, i.e. the timestamp, will be removed from the date , allowing data aggregation on a date level. Using this function can reduce the number of rows and thus, prevent the line chart from becoming truncated. Despite the change in granularity, it may still generate a lot of data points.
- weekStartDate() - This function aggregates data to the week and still allows you to view the trend over time. This is a good level of granularity to show data across years.
- dateTrunc() - To further reduce the volume of data, you can use the dateTrunc() function.
Spoiler
Why can I not use month() or select "Month" from the Date Part?
The month() or the Month selection from the Date Part returns the discrete month of the year in a number. Example: A five-year dataset will show 12 months of data - all five January months aggregated together, all five February data, etc. For that reason should be used if the data is already filtered for a single year or you are grouping the data by years. Otherwise, it may be used for viewing the monthly breakdown for viewing the seasonality across years.
The month() or the Month selection from the Date Part returns the discrete month of the year in a number. Example: A five-year dataset will show 12 months of data - all five January months aggregated together, all five February data, etc. For that reason should be used if the data is already filtered for a single year or you are grouping the data by years. Otherwise, it may be used for viewing the monthly breakdown for viewing the seasonality across years.
Year-over-year comparison
To view a year-over-year comparison, leverage the coloring dimension to split the data by year. It also can be produced easily by using the year() function or by selecting "year" in the date part drop-down.
Note that the grouping dimension cannot be set to date or a regular timestamp. Data from the same day of the year but from different years will not be shown together. As an alternative, you can build formulas that use the following functions:
- dayofyear() - This function will return a number between 1 and 366. It will show the data by the day of the year but across years. Since it returns a continuous value, the line chart will show the x-axis properly with the scale of days of the year from 1 to 365.
- weekNum() - data will be aggregated by week. It can still allow you to show data over a year with sufficient granularity. However, this approach may not be a good idea if you want to see the data pattern within a week
- month()
- quarter()
Spoiler
How can I show the date as the tooltip?
When the data is shown as day of the year, it is hard to know which month or the data of the month a given point is showing. In this situation, you can show the date in a tooltip.
When the data is shown as day of the year, it is hard to know which month or the data of the month a given point is showing. In this situation, you can show the date in a tooltip.
Since the tooltip result value is also based on the result of grouping by the grouping dimension and coloring dimension, you can change the aggregation rule from COUNT to MAX or MIN.
Month over Month comparison
day() - this function returns the day of the month. The value can be 1 to 31. We can view the data by days within a month.
The chart can be grouped by months using one of the following ways.
You can select the date column and use "Month" in the Data Part. It will show the month number as a value from 1 to 12.
Alternatively, you can use the formatDate() function to show the month's name.
Spoiler
Can I use formatDate to get the day of the month? Isn't it the same thing?
The formatDate function returns the data type String even though it also returns the number between 1 and 31.
Here is the outcome if we use formatDate(< date>, 'd') to get the day from the data column.
There are two problems with the result set. First, the data is sorted in alphanumeric order instead of numeric order. Second, if a day has no data associated to it, Incorta will not display it in the the axis. The result of these two issues is that you cannot view the data trend.
The formatDate function returns the data type String even though it also returns the number between 1 and 31.
Here is the outcome if we use formatDate(< date>, 'd') to get the day from the data column.
There are two problems with the result set. First, the data is sorted in alphanumeric order instead of numeric order. Second, if a day has no data associated to it, Incorta will not display it in the the axis. The result of these two issues is that you cannot view the data trend.
Related Material
