Toucan 3.0
YouPrep documentationHelp centerGet a demo
  • Welcome
    • ๐Ÿ‘‹Welcome to Toucan
    • โš™๏ธTechnical resources
      • โš™๏ธToucan stack
      • Setup mode
        • Toucan SaaS mode
      • โš™๏ธSecurity
        • Application Security
        • Source Code Quality
        • Global Security Practices
        • Security of Docker Images
  • TUTORIALS
    • ๐Ÿ“ŠGetting Started : Embedded Analytics
    • ๐Ÿค“Advanced tutorials
      • Embedding a story with user attributes
        • Dynamic filter with user attributes
        • Dynamic Tables
        • Dynamic Database
        • Dynamic Host
      • Using the HTTP API connector in advanced use cases
      • Using advanced syntax for SQL queries
      • Merging filters with our tool
      • Deep customization chart (CSS)
        • Homepage customization
        • Chart customization
        • Dashboard customization
  • Data Management
    • ๐ŸงฎOverview of Data In Toucan
    • ๐Ÿ“กDatasources in Toucan
      • ๐Ÿ”ŒManaging Connectors
        • ๐Ÿ”ŒCreating, editing and deleting a connector
        • ๐Ÿ”ŒSet up OAuth2 credentials for your platform
        • ๐Ÿ”ŒSetting up a connector
          • ๐Ÿ”ŒGeneric Connectors
            • ๐Ÿ”ŒSetting up an HTTP API connector
            • ๐Ÿ”ŒSetting up an ODBC Connector
          • ๐Ÿ”ŒDatabase and data warehouse Connectors
            • ๐Ÿ”ŒSetting up an AWS Redshift Connector
            • ๐Ÿ”ŒSetting up a Snowflake Connector
            • ๐Ÿ”ŒSetting up a PostgreSQL Connector
            • ๐Ÿ”ŒSetting up a Google Big Query Connector
            • ๐Ÿ”ŒSetting up an AWS Athena connector
            • ๐Ÿ”ŒSetting up a MySQL connector
            • ๐Ÿ”ŒSetting up a MongoDB connector
            • ๐Ÿ”ŒSetting up a Microsoft SQL Server connector
            • ๐Ÿ”ŒSetting up an Azure SQL connector
            • ๐Ÿ”ŒSetting a Databricks Connector
            • ๐Ÿ”ŒSetting up a ElasticSearch Connector
            • ๐Ÿ”ŒSetting up a Clickhouse Connector
          • ๐Ÿ”ŒOnline services connectors
            • ๐Ÿ”ŒSetting up a Sharepoint Connector
            • ๐Ÿ”ŒSetting up a Google Sheets Connector
            • ๐Ÿ”ŒSetting up a Salesforce Connector
            • ๐Ÿ”ŒSetting up a Hubspot Connector
          • ๐Ÿ”ŒSetting up an AWS S3 connector
      • ๐Ÿ“Managing Files
        • ๐Ÿ“Adding, editing and deleting local files
        • ๐Ÿ“‚Using advanced file settings
        • ๐Ÿ“Adding and combining remote files in Toucan
    • ๐Ÿ”ขDatasets in Toucan
      • ๐Ÿ”ขStored and Live Datasets
      • ๐Ÿ’ฟManaging datasets
        • ๐Ÿ”ขCreating datasets
        • ๐Ÿ”ขEditing, Duplicating and Deleting a dataset
        • ๐Ÿ”ขRefreshing and Publishing Datasets
        • ๐Ÿ“ˆOptimize data performance
        • ๐Ÿ—‚๏ธAdding indexes to stored datasets
        • ๐Ÿ‘ฉโ€๐Ÿ’ปCode mode and single mode
      • ๐Ÿ›‘Setting permissions on dataset
      • ๐Ÿ—ƒ๏ธMaintaining Data
        • ๐Ÿ—ƒ๏ธTagging datasets
        • ๐Ÿ—ƒ๏ธIdentifying datasets dependencies
        • ๐Ÿ—ƒ๏ธSet validation rules
    • ๐Ÿง‘โ€๐ŸณPreparing data
      • Overview of YouPrepโ„ข
        • ๐ŸŽนColumn header
          • Rename column
          • Duplicate column
          • Fill null values
          • Replace values
          • Sort values
          • Convert columns data types
        • Add
          • Add text column
          • Add formula column
          • Add conditional column
        • Filter
          • Delete columns
          • Keep columns
          • Filter rows
          • Top N rows
          • ArgMax
          • ArgMin
        • Aggregate
          • Group by
          • Add total rows
          • Hierarchical rollup
          • Get unique groups/values
        • Compute
          • Compute evolution
          • Cumulated sum
          • Percentage of total
          • Rank
          • Moving average
          • Compute statistics
          • Absolute value
        • Text
          • Concatenate
          • Split column
          • Extract substring
          • To lowercase
          • To uppercase
          • Compare text columns
          • Trim spaces
          • Replace text
        • Date
          • Convert text to date
          • Convert date to text
          • Extract date information
          • Add missing dates
          • Compute duration
        • Reshape
          • Pivot
          • Unpivot
          • Waterfall
        • Combine
          • Append datasets
          • Join datasets
        • Geo
          • Geographic dissolve
          • Geographic hierarchy
          • Geographic simplify
          • Prepare geo data (with basemap)
      • YouPrepโ„ข Native SQL
      • Hybrid pipeline
    • โžฟManaging variables in Toucan
      • โžฟVariables hub
      • โ™ˆUse variables in YouPrepโ„ข
      • โžฟEasy reference to variables
    • ๐ŸงžUsing advanced data concepts
      • ๐ŸงžData personnalisation with user attributes
        • Connector setup with a user attribute
        • Database selection with a user attribute
        • YouPrep data filtering with a user attribute
        • Filter data in SQL with a user attribute
      • ๐ŸงžAdvanced syntax for variables
      • ๐ŸงžData cache
  • Visualizations and Layouts
    • ๐Ÿ“บApps
      • ๐Ÿ“บManaging Apps
        • โž•Creating Apps
        • ๐Ÿ“„Duplicating Apps
        • ๐Ÿ–จ๏ธPublishing Apps
        • ๐ŸšฎDeleting Apps
        • โœ๏ธEditing within an App
      • ๐Ÿ–Œ๏ธCustomizing Apps
        • Customizing chart color elements
        • Customizing the app's font
        • Adding Assets
        • Adding Glossary
        • Setting up, Managing and testing custom visibilities
        • Customizing the "no data error" message
        • Creating a dynamic background based on an Filter's column
      • ๐Ÿ Home
        • Creating the Home
        • Creating Tiles
          • Tile Dynamic Value
          • Tile Leaderboard
          • Tile Line
          • Tile Scorecard
          • Tile Bullet
          • Tile Heatmap
          • Tile PDF
          • Tile Video
          • Tile Image
          • Tile Text
          • Tile HTML
          • Tile Separator
      • โœจStories
        • Creating a Story
        • KPIs
        • Narrative
        • Crossfilter
      • ๐Ÿ“ฝ๏ธFilters
        • Managing Filters
          • Creating, reusing and editing Filters
          • Applying Filters
          • Unpinning and deleting Filters
        • Type of Filters
          • Dropdown
          • Checkboxes
          • Buttons
          • Date Range
          • Hierarchical
          • Slider
        • Templating from Filters' values
        • Dependant Filters
      • ๐Ÿ“ˆPDF Report
      • ๐ŸŽกDatawall
      • ๐Ÿ—๏ธDashboard Builder
        • Create a Dashboard Builder
        • Embed a Dashboard Builder
        • Dashboard export options
      • ๐ŸŒŸMyFavorites
    • ๐Ÿ“ŠCreating Visualizations
      • ๐ŸคฉViz Gallery
        • Barchart
        • Barlinechart
        • Bubblechart
        • Bulletchart
        • Circularchart
        • Funnelchart
        • Gantt chart
        • Heatmap
        • HTML
        • Leaderboard
        • Leaderboard Centered Average
        • Linechart
        • Mapchart
          • Configure a drill
        • Mediachart
        • Radarchart
        • Tablechart
        • Timeline
        • Versuschart
        • Waterfallchart
        • Score Card
        • Stacked Barchart
      • ๐Ÿง Common Chart Configuration
      • ๐Ÿ’…Customizing chart colors
      • ๐Ÿงžโ€โ™‚๏ธAdvanced chart configuration
        • Templating from chart's dataset
        • Add units, precisions and sentiments
        • Adding Tutorials
        • Add sparklines
        • Navigate with stories
        • Group informations in your stories
        • Multiple charts in one story
        • Manage dates
        • Customize tiles' sources
        • Add stars to tiles' title
        • Manage data order in your tiles
        • Navigate with tiles
    • ๐Ÿ‘ฉโ€๐Ÿ’ปEmbedding
      • ๐Ÿ”Authentication
      • ๐Ÿ–‡๏ธIntegration
        • Generate and manage embeds
        • Customize embeds
        • Embedding a Toucan App Using iFrames
        • Passing Extra Variables to Your Toucan Embed
      • โš™๏ธEmbed SDK
        • Embed SDK Authentication
      • โ“FAQ
    • ๐Ÿ™‹Self-Service
      • Self-Service Dashboard
      • Self-service PDF Report
  • Collaboration
    • โฐCreating alerts
    • ๐Ÿ“งManaging notifications
    • โž•Enriching a story with descriptions
    • ๐Ÿ’ŒSharing content
    • ๐Ÿ’ฌAdding comments to stories
  • Administration
    • Page
    • โš™๏ธInstance Management
      • โš™๏ธManaging operations in SaaS
      • โš™๏ธCustomizing your instance (whitelabel)
    • ๐Ÿ‘ฅManaging Users
      • ๐Ÿ‘ฅUsers
      • ๐Ÿ‘ฅManaging user groups
      • ๐Ÿ‘ฅManaging user properties
      • ๐Ÿ‘ฅSetting up permissions and visibilities
    • ๐ŸŒManaging languages in Toucan (internationalisation)
    • ๐Ÿ“ˆMonitoring Engagement with User Analytics
      • ๐ŸŽ›๏ธHow to Filter your User Analytics?
      • ๐Ÿ–ฅ๏ธUnderstanding your User Analytics Dashboards
  • Additional Ressources
    • ๐Ÿ“šExternal documentation
    • ๐ŸšSupport for App-builders
    • ๐Ÿ†•Latest releases
      • ๐ŸŽ2025 Releases
      • ๐ŸŽ2024 Releases
      • ๐ŸŽ2023 Releases
    • ๐Ÿ”งTroubleshooting
      • Troubleshoot:: DataHub
      • Cross-Site Cookies
      • How to :: read the inspector error
      • How to :: troubleshoot the toucan way
Powered by GitBook
On this page
  • Overviewยถ
  • Ensure columns have the appropriate typeยถ
  • Avoid dealing with timezones (when possible)
  • Use date parametersยถ
  • Order datesยถ
  • Other cool dates stuffยถ

Was this helpful?

  1. Visualizations and Layouts
  2. Creating Visualizations
  3. Advanced chart configuration

Manage dates

How to :: manage your dates format

Last updated 1 year ago

Was this helpful?

Overviewยถ

You will often have to play with dates in your Toucan application and there are a few tips you need to know to become a Toucan date master ! ๐Ÿ“…

There are 2 steps you will likely use to display dates in your charts:

  • in the dataset editor : make sure your date column is properly interpreted

  • in the configuration of your graph : decide how you want the date to be displayed

Ensure columns have the appropriate typeยถ

โ€œDateโ€ is a specific data type, like numeric, text or boolean. When crafting a query, you can easily check if a column has the โ€œdateโ€ type by looking at the icon next to its header:

If itโ€™s not, either:

  • or cast the column directly by clicking on the type icon.

Avoid dealing with timezones (when possible)

Toucan apps are global: they can be visited from users all around the world. Meaning all users wonโ€™t necessarily be on the same timezone. But we still want them to easily agree on the data they see and be able to talk about it! So to avoid mental calculation with timezone offsets and any related misunderstanding, Toucan will display dates the same for all users, wherever they are.

In a nutshell :

  • dates provided without timezone information are stored and displayed as is

  • while dates provided with timezone information are stored and displayed as GMT dates

Timezone-less dates

This is the most common use case, both for flat files (Excel/CSV) & databases because:

  • people donโ€™t manually input dates with timezone info in spreadsheets (at least 99% of the time)

  • the SQL standard requires the โ€œdefaultโ€ datetime type to be equivalent to โ€œtimestamp without time zoneโ€ & databases like Snowflakes or PostgreSQL honors that behavior

So when providing dates without timezone information, like 21/03/2006 21:50:00, Toucan will display 21/03/2006 21:50:00 (aka the same date), no matter where you are on the planet. The current user timezone is not taken into account when displaying dates, period.

Dates with timezone information

This use case appear when using data from a database or an API that has such info encoded in its dates. It may not play well with the โ€œdates-in equal dates-outโ€ behavior but there is ways to help mitigating this case for most users.

When providing dates with an explicit timezone offset like 2006-03-21T21:50:00.000-07:00 (GMT-7 offset), they will be stored and displayed as their GMT equivalent (here 2006-03-22T05:50:00), no matter where you are on the planet again.

Also, if your dates already are already on GMT, either explicitly like 2006-03-22T05:50:00.000Z (โ€˜Zโ€™ mean GMT) or implicitly as a GMT timestamp like 1143006600, they will also be stored and display as is.

Tip

If you have enough control over your input data, you should consider providing your dates to Toucan directly in the timezone youโ€™d like them to be displayed (like 2006-03-21T21:50:00) but without any timezone info (like a -07:00 offset).

If not applicable and you โ€œonlyโ€ need to display dates in a single timezone other than GMT, you can shift them using the following succession of data transformation steps :

  • convert dates to numbers, this will give you the corresponding GMT milliseconds timestamp

  • use the formula step to apply your timezone offset, in milliseconds (offset_hours * 60 * 60 * 1000)

  • convert the result from numbers to dates again and voilร 

This is definitely not perfect as it will not take daylight savings into account.

If youโ€™re using this kind of trick, weโ€™d really like to here more about your use case!

Note

If you have a use case where youโ€™d like to display dates in line with the current user timezone, please contact us, weโ€™d like to here from you !

Use date parametersยถ

Once Toucan understands your column is a date column, you can apply date parameters. This will allow you to modify how the date is displayed for your end-users for example.

Charts parametersยถ

You might want to change the way dates are displayed by default in your charts. In order to do just that, our linechart, particularly well-suited to display dates, has a built-in options that will allow you to manage dates. Itโ€™s called the ticks parameters.

In your optional parameters, you will be able to setup :

  • X Ticks Format (tickformat in code mode) : write here the format you wish to be displayed according to the same syntax we saw previously (ex: โ€œ%Yโ€)

  • Hide Start and End Ticks (hideXTickLimits in code mode) : turn on this option to hide the first and last tick

  • Display all X Ticks (displayAllXTicks in code mode) : by default, Toucan displays a certain amount of ticks according to design rules. If you wish all you ticks, turn on that option

Two other options only available in code mode for the linechart are :

  • dateFormat : write down the date format you wish to display in the legend

  • selectLastDate : turn on that option to always display first the last date available

Note

For the barchart, stackedbarchart and the barlinechart, the tickformat parameter is also available in code mode. Check their reference pages :

Postprocessยถ

If your chart does not have a tickformat parameter and you need to modify how itโ€™s diplayed, no worries ! We have a postprocess function to help you out :

Just define the output format you want!

Filters / Requestersยถ

The filter slider or a requester is especially recommended to display dates. It has specific options you can use with dates :

  • you can put the โ€˜defaultโ€™ parameter on โ€œlastโ€ to set it on the last date by default

  • you can order it on specific column

Order datesยถ

If your dates need to be ordered, use the order option of your graph like labelsOrder or groupsOrder for example.

Make sure the date is either displayed in the following format:

  • Year - month

or

  • Month - day

Why ? Because the order column sorts ascendingly and that way dates will be properly sorted. Ex: 201801 will be before 201802

Other cool dates stuffยถ

  • You can add aggregation columns based on the date : check out these postprocess functions

change the configuration of the data source ( or connector) to make sure dates are understood as such,

Check out our function.

some parameters

Date is one of the 2 most structuring variable you can define in a Toucan App : learn how to build the best

: to compute to date values;

: to compute evolution values

๐Ÿ“Š
๐Ÿงžโ€โ™‚๏ธ
file
barchart
barlinechart
stacked barchart
change_date_format
date format
date selector
compute_cumsum
compute_evolution_by_frequency
check if a column has the โ€œdateโ€ type by looking at the icon next to its header
make sure dates are understood as such
change the way dates are displayed by default in your charts
check if a column has the โ€œdateโ€ type by looking at the icon next to its header
make sure dates are understood as such
change the way dates are displayed by default in your charts