This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
When bringing data into Alteryx a lot of users often add a select tool to check the data type and structure. Data types are very important because of the available operations/functions in tools can be adjusted to fit the data type being used!
Date/Time data can appear in your data in string formats (text fields) or date formats. The DateTime Tool standardizes and formats such data so that it can be used in expressions and functions from the Formula or Filter Tools (e.g. calculating the number of days that have elapsed since a start date). It can also be used to convert dates in datetime format to strings to use for reporting purposes.
Users often ask, "How do I do (x) function in Alteryx?" - here's a handy guide for translating Tableau functions for use in Alteryx!
*Please note: This is not a comprehensive list of all functions available in Alteryx and Tableau - only functions that are written differently, but perform similar tasks, are included here. For a list of all the functions available in Alteryx, please refer to our Help Documentation.
Return smallest integer greater than or equal to [x]. Works like the 'RoundUp' function in Excel.
x % y
Modulo of n divided by d - The Modulo operation finds the remainder of division of one number by another number.
Return [x] raised to the [e] power.
Return [x] rounded to nearest multiple of [mult].
IF isnull([field]) THEN 0 else [field] ENDIF
Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.
IF Contains([field], "string") then 1 ELSE 0 ENDIF
Returns true if the given string contains the specified substring.
FIND(string, substring, [start])
Searches for the occurrence of a particular string within a data field and returns the numeric position of its occurrence in the string. In Tableau, returns the index position of substring in string or 0 if the substring isn't found. If the optional argument start is added, the function ignores any instances of substring that appear before the index position [start].*
Return the length of the string [x].
Converts a string to lower case.
REGEX_Match(string, pattern, icase)
Searches a string for an occurrence of a regular expression.
REGEX_Replace(string, pattern, replace, icase)
REGEXP_REPLACE(string, pattern, replacement)
Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string.
Substring(x, start, length)
MID(string, start, [length])
Return the substring of [x] starting at [start] and stopping after [length], if provided.*
Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace.
Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace.
Converts a string to upper case.
Date Time Functions
DateTimeAdd(datetime, interval, units)
DATEADD(date_part, interval, date)
Return the given date/time modified by the given duration. The <interval> specifies a positive or negative integer of time to add or subtract and <units> is one of a date/time unit - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed.
DateTimeDiff(datetime1, datetime2, units)
DATEDIFF(date_part, date1, date2, [start_of_week])
Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified units - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed.
DateTimeTrim(datetime, trim type)
DATETRUNC(date_part, date, [start_of_week])
Remove unwanted portions of a date/time and return the modified date/time. Options include: firstofmonth, lastofmonth, year, month, day, hour, minute. In Tableau, truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter is optional.
Returns the current system date and time.
DateTimeParse(datetime, format of incoming string)
MAKEDATE(year, month, day)
Converts a date string with a specific format to the standard ISO format yyyy-mm-dd HH:MM: SS. In Tableau, returns a date value constructed from the specified year, month, and date.
Returns today’s date. The time is set to midnight of the beginning of the day. Returns the current date.
ToNumber(x, bIgnoreErrors, keepNulls)
INT(expression) or FLOAT(expression)
Converts a string parameter to a number. The second parameter is optional and allows for ignoring conversion error messages. This parameter is a boolean flag and will accept a value of 1, 0, true or false. There is an optional 3rd parameter to handle Nulls. In Tableau, INT casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero. FLOAT casts its argument as a number with decimal/float precision.
Casts its argument as a string.
*In Alteryx, string positions start at 0. In Tableau, string positions start at 1.
The Excel number for a modern date is most easily calculated as the number of days since 12/30/1899 on the Gregorian calendar.
Attached please find a v10.0 workflow that converts a double (Excel date) to a Datetime in Alteryx.
It uses this function in a Formula Tool:
where Datebase = a datetime value set to "1899-12-30 00:00:00"
and Field1 is double value = 42397
Recently a couple questions came across the Customer Support desk asking how a fiscal calendar could be incorporated into a workflow. Natively Alteryx doesn’t have a tool to create one, but Alteryx does have a number of tools to make a fiscal calendar. Here is an example of how this can be done.
1. Determine the start and end dates of the fiscal calendar and enter them into Text input tool, each on an individual row
2. Connect a TS Filler tool to generate dates between the start and end dates
3. A Select Tool was added to remove unnecessary fields
4. Add a Multi-Row Formula Tool to create a Day of Week field by assigning each row a day of the week from 1 to 7
5. Add another Multi-Row Formula Tool to calculate the fiscal week. Whenever the Day of Week is 1 add a value of 1 to the previous row’s fiscal week value. This will create a running week value for the entire year
An example workflow is attached. Also in example workflow is an example of how the fiscal month and week of month may be added. (Workflow is in Alteryx 10.6 version.)
The Date Time Now Tool is part of the Input Tool Category and it is actually a macro encapsulating other Alteryx tools . To use it, only one selection needs to be made: an output format. That's it, then you can go about your business. You also have the option to output the time with that date.
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!
In 1925, the small town of Nome in Alaska suffered a diphtheria outbreak. Out of usable antitoxin and in the depths of winter, the town doctor telegrammed the governor in Juneau and authorities in Washington D.C. requesting a million units of antitoxin to stave off an epidemic. Confronted with the task of saving the people of Nome, Alaskan authorities debated their options of getting the antitoxin to Nome. With sub-zero temperatures, limited daylight and a lack of experienced pilots, airlifting the supplies was a less than ideal solution. Instead, a dogsled relay was selected to transport the antitoxin across the 674 miles from Nenana to Nome. The mushers endured near-blizzard conditions and hurricane-force winds to deliver the serum five and a half days later, saving the small town from a deadly outbreak.
While you may not have the fate of a small town on your hands, understanding the time elements in your data is critical for your analyses. Perhaps you need to determine a product’s time in transit between two ports or warehouses, identify non-business days in your dataset to correctly account for time, or re-format data to work on a 12 or 24 hour clock. In all of these cases, time is of the essence!
To demonstrate the use of the Formula Tool’s DateTime functions, and in the spirit of the Alaskan dogsledding tradition, we’ll work with data from the last leg of the Iditarod, which stretches from Safety to Nome. Using the data provided from the 2016 race, we will 1) calculate the elapsed time for each musher between the two checkpoints, 2) determine the Iditarod’s start date and time from a musher’s total race time and arrival time in Nome, and 3) re-format the time format from a 24 to 12 hour clock. Before beginning with Formula Tool’s DateTime functions, make sure that your data should be appropriately formatted as a DateTime field type using the DateTime tool or a Select Tool.*
*The only acceptable string format for use with DateTime functions is yyyy-mm-dd HH:MM:SS, which is Alteryx’s native string format.
The Input data contains the time each musher left Safety (Departure) and the time that they arrived in Nome (Arrival), finishing the race. To calculate the time it took for each musher to complete this final leg of the race, we’ll find the difference between the start (Departure) and end (Arrival) times using the DateTimeDiff function:
DateTimeDiff (<date/time1>, <date/time2>, <units>)
[Arrival] [Departure] ‘seconds’
The expression returns the elapsed time between the Arrival and Departure times as years, months, days, hours, minutes or seconds as a numeric field type. In this case, the expression returns the number of seconds (Int64) between the Departure and Arrival times, which can be used downstream to calculate hours (Seconds/3600) and minutes (Seconds/60).
The sample dataset not only provides data for the final leg of the 2016 Iditarod but also each musher’s time for the entirety of the race (Total Race Time). Using these pieces of information, we can determine the time at which the mushers started the race in Anchorage by subtracting the musher’s total race time from their completion time (Arrival) in Nome. After a bit of parsing and simple math, we can convert a Total Race Time of 8d 11h 20m 16s to its value of 732016 seconds (Total Seconds). Then, we can subtract the race time (in seconds) from each musher’s arrival time in Nome to determine the start time of the race using the DateTimeAdd function:
DateTimeAdd (<date/time>, <interval>, <units>)
[Arrival], -[Total Seconds]*, ‘seconds’
*Because we are looking to determine a DateTime in the past, we use a negative value for the time interval. When looking for a DateTime in the future, ensure that your time interval is a positive numeric value.
After applying our DateTimeAdd function, we find that the 2016 Iditarod began at 2016-03-06 15:00:00. That’s not the easiest format to understand, so let’s take advantage of the power of the DateTime Format function. The DateTime Format tool allows you to indicate a format specifier to parse components of a DateTime field. A format specifier begins with a percent sign (%), followed by a case-sensitive letter. For a complete list of supported format specifiers, please refer to the Formula Tool’s help documentation.
For our particular example, let’s identify the day of the week, the month, date and time (on a 12 hour clock) that the race started. We use the format identifiers to create a string field that contains this information. It is important to note that each format specifier is treated as text and needs to be wrapped in single quotes (see example below).
Using a series of format specifiers, we can see that the 2016 Iditarod began on Sunday, March 6, 2016 at 3:00 PM. When time, whether when dogsledding or managing your business operations, is of the essence, you can count on the DateTime functions to save the day!
Question How do I calculate the number of business days between two dates?
Answer We get this question a lot in Customer Support. It's actually fairly easy to do using a just a few tools.
Assuming your beginning and end date are in two separate fields you can take a look at the sample attached to demonstrate the process.
1) Use the Generate Rows tool to fill in the missing dates
2) Use a Formula tool to create a field with the day of the week for each of the filler dates
3) Use a Filter tool to remove the unwanted days of the week
4) Use the Summarize tool to count the number of days that are left
Note that if you don't already have an unique ID on each record, you'll want to use the Record ID tool to add one to make the summarize process easier.
The sample workflow was built in 10.6, but the same general process can be followed in all versions.
Question If I have a list of dates, how can I find the date of the next Monday for each date?
Answer Using a simple workflow, you can calculate the next Monday from any date by using a single Formula tool and configuring as follows:
Determine the day of each date Day: DateTimeFormat([Sample Dates],"%a")
Calculate the number of days to get to the next Monday based on each day AddDays: Switch([Day],Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1)
Add the number of days (from step 2) to get to the next Monday to each date Monday: DateTimeAdd([Sample Dates],[AddDays],"days"
Verify that new date is Monday VerifyNewDay: DateTimeFormat([Monday],"%a")
You can actually do all of this within one formula, save for verifying the day, if you want to get fancy:
Monday: DateTimeAdd([Sample Dates], Switch(DateTimeFormat([Sample Dates],"%a"),Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1),"days")
Things to consider:
This workflow assumes that your dates are already in the Alteryx native date format of "YYYY-MM-DD". If they aren't, please visit another Knowledge Base article, Date Conversions, for tips on how to get your dates there!
This was done in 10.1. Sample workflow attached.
Thanks for tuning in!
Business Problem: Data preparation is time consuming and inefficient. Analysis requires multiple formats and types of data to be brought together within a streamlined process. Data warehouses are often incompatible silos of information that are both difficult and impractical to integrate with other data sources. Actionable Results:
Quickly and easily parse complex data streams
Integrate data from various disparate sources
Create scalable and repeatable data cleansing workflows
Overview: Effective strategic analysis often involves the integration of several types of disparate data formats. In the past, this has been an arduous task, and could pose a daunting challenge to even the most experienced analysts. Alteryx simplifies data hygiene techniques by providing streamlined functionality as both an ETL and processing tool. With modules that are simple to customize and run, tasks such as data parsing become feasible in a short amount of time. Any type of data cleansing can be easily accomplished using Alteryx, and every workflow created becomes scalable and repeatable. This module demonstrates how virtually any data stream can manipulated and formatted with relative ease. Vertical: Applicable to any vertical Key Tools Used: Text to Columns,Transpose, Multi-Row Formula, Summarize (Group By )
Lookup the term "date" in community and you'll get 2,000 results. Hidden in this article are Easter eggs for the finding. Besides learning about dates, see if you can uncover the clues that I've cloaked.
You probably already know that you can output results to multiple sheets of an Excel file. If not, you should check out our resource that explains how to do that very thing. But what if you run that workflow every day, and you want to keep the outputs from days past?
How do I capture values that are not recognized as dates? I am importing data from an Excel file. Two of the fields are supposed to contain dates, but because the values are entered by hand sometimes they aren't entered in a way that is recognizable by Alteryx as a date. For example, "11/8/`6" is entered instead of "11/8/16". I want to divert the records that could not be read into a separate workflow.
Convert the date field first. If the date does not convert, a NULL is produced. Use a Filter Tool based on finding NULLs to create a separate stream:
See v10.5 workflow attached.
In this posting, we'll take a look at Excel functions that return today's date and current time. Then we'll see how to use Alteryx to do the same thing. We'll take this a step further and show how Alteryx can be used to return a large number of date-related information for any date using macros and apps.
To get today's date in Excel, you use the =TODAY() function.
And the =NOW() function will return today's date and time.
You can format date and time the way you'd like (eg. Nov-10 or November 10, 2016 instead of 11/10/2016).
Alteryx also has a couple of ways to get today's date and time. The first is macro available in the In/Out toolset called 'Data Time Now'. The tool's configuration provides many options for how you'd like to see the data, including date as well as date and time.
The other method is to use a tool (such as the Formula Tool) where an expression can be used with the function 'DateTimeNow()':
Use the same method if you want just the date or time.
There is similar function called 'DateTimeToday()' which will return the current data as of midnight (so the time comes back as 00:00:00).
What if you want information about a date other than today, however? I've written about calendar and date aggregation before and have made a calendar macro available for anyone to use. If you have a date in yyyy-mm-dd format, you join it to the Date field in the macro which returns the following fields:
Date: yyyy-mm-dd format; includes every day beginning 2000-01-01 through 2099-12-31.
Year: yyyy format.
Quarter: numeric representation of quarter (1, 2, 3 or 4 rather than Q1, Q2, Q3 or Q4).
Month: numeric representation of month; NO leading zeros.
MonthName: January, February, March, April, May, June, July, August, September, October, November and December; completely spelled out rather than an abbreviation.
WeekNumber: numeric representation of week; generally values range from 1–52, but occasionally a year will have a week 53; weeks 1 and 52 (or 53) may be partial weeks (i.e. less than seven days).
Day: corresponds to the calendar date with values from 1-31 (for months with 31 days); NO leading zeros.
DayName: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday; spelled out rather than abbreviations; Sunday is the beginning of the week.
DayYear: day of year; values range from 1-365 except for leap years which have a day 366.
DayQuarter: day of quarter; values range from 1-92.
DayWeek: numeric representation of week where 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday and 7=Saturday.
Week StartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.
Week EndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).
Month StartDate: date in yyyy-mm-dd format and data type = Date.
Month EndDate: date in yyyy-mm-dd format and data type = Date.
Quarter StartDate: date in yyyy-mm-dd format and data type = Date.
Quarter EndDate: date in yyyy-mm-dd format and data type = Date.
I've taken this a step further and created an app with the calendar macro embedded in it which allows a user to select a date and the fields they want returned at run time.
I've made a couple of version of the Calendar macro; one where the week begins on a Sunday and the other where the week begins on Monday. In the attached app, the macro where the week begins on a Sunday is used but can be easily replaced by the one beginning on Monday.