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 I'm out showing my customers some of the capabilities of Alteryx, I find that one question that always seems to get a "yeah!" response is "Any issues with date formatting?".
Now while this subject has probably been covered in the Alteryx KB elsewhere, I thought I would deal with a specific question that I just got this morning that can be the 'platform' for handling a lot of situations.
The question was "How do I get from a date like this...MM/DD/YYYY...to where I have the day integer, the month name, and the year in three different columns?"
Most Alteryx users know that there is a DateTime tool in the Parse category, but they also have found that all date formats aren't necessarily included there. What I have found that many users don't know is how flexible date formatting is when using a couple of the DateTime functions in the Formula tool.
These two functions are DateTimeParse and DateTimeFormat. The first one takes a String data type that represents a date and/or time and returns the value in a DateTime data type (you select the data type for it to return). The second one takes a Date, Time, or DateTime data type and returns a string in the format designated. The key to using either of these are the various 'specifiers' you can use within them. (You can find the list of specifiers in the DateTime Operations Help article.)
So for the above question, you are starting out with a string in a given format and basically want to get it into a string with a different format (and then separate it into columns).
The formula expression for this would be...DateTimeFormat(DateTimeParse([Date],'%m/%d/%Y'),'%d|%B|%Y')...where I am actually nesting the one function within the other. The 'inside' function takes the string and converts it to a Date type, and the 'outside' function converts it back to a String. As you can see, it doesn't matter what 'separators' you have for the dates...in this case I used the 'pipe' symbol because I like to use it in the Text to Columns tool (which is the tool I use to separate the date parts into columns), but many date formats use dashes, periods, or even spaces.
I use these two functions in combination like this all the time to get from one date format to another. And if you check out the Help article mentioned above, you will see there are specifiers for Day of the Week (which allows you to create a process that gives you only business days), 24-hour clock, AM/PM indicators, and even time zone.
So having worked with dates and times from a number of different data sources, I know how 'weird' some of them can be. These two Alteryx DateTime functions make bringing in that data and using it in analysis MUCH easier!
I've attached an example that deals with the specific question above. Hope this helps!
Sr. Solutions Engineer
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?
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.
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.
Question How do you calculate Month Start Date, Month End Date, Quarter and Week Number?
Alteryx has the ability to calculate the quarter, week number, etc:
The attached workflow uses several of the Date Time functions: (See attached workflow)
DateTime FirstOfMonth() Returns the first day of the current month, at midnight.
DateTime LastOfMonth() Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
Quarter formula - if [Month] IN (1,2,3) THEN [Year]+' '+'Q1' elseif [Month] IN (4,5,6) THEN [Year]+' '+'Q2' elseif [Month] IN (7,8,9) THEN Year]+' '+'Q3' else [Year]+' '+'Q4' endif
Week Number - ToNumber(DateTimeFormat([Date],'%U'))+1
Visit our help documentation for detailed informaiton about Date Time Functions. The example above is attached in v10.5.
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!
For example, when using the basic filter tool string fields will be populated within string operators such as ‘equals’ or ‘contains’ whereas, a numeric field with have operators such as ‘>=’ or ‘isnull’.
How to check/change your data types?
Auto Field Tool
Use the auto field tool to evaluate your data set on each run and let Alteryx decide what the type and size should be. As or if your data changes the auto field tool will adjust the type and size it sets. This is especially useful when looking at speeding up data processing because Alteryx will look to assign the most efficient size and data type.
This tool will not be perfect and there are some instances where you may not want Alteryx to decide, for example a field of zip codes will appear to Alteryx to be numbers and Alteryx will set it to a number type and you will lose all your zeros at the beginning of your Massachusetts zip codes. In this case you may want to use a select tool.
Using the select tool means that Alteryx will not evaluate the data and data that doesn't fit the new, specified, type is replaced with [Null].
When considering field size make sure you are familiar with the length of the string as it will be truncated if it exceeds the length of the string.
For Numeric types the Size is not configurable, but if you are looking for precision please use Fixed Decimal.
Data that is too long is rounded (usually to 6 decimal places).
Fixed Decimals are the exception - you use the format of length of number dot followed by number of decimal places.
What happens when I bring my data into Alteryx?
Data types are applied to the entire column/field and each value within that column.
Alteryx will automatically map incoming types from Databases and Structured Files (Excel) to the best one for the type within an Alteryx table.
Strings Data Types
Strings are anything that you want to keep as it is. A String is any combination of characters, alpha-numerics and symbols. You'll also use a string type if the field is of mixed values that is has numbers in one cell and words in another.
String and Wstring are set lengths.
The V_string types are variable in length. The amount of memory reserved for processing varies.
Wstring will capture more international characters such as Japanese or Chinese.
V_String is favorable because it is flexible enough to not truncate fields but will also maximize processing time due to adjusting string size length.
Numeric Data Types
Numbers or numeric fields only contain numbers. BEWARE numeric field types and values with leading zeros (such as 01 - the zero would be dropped and the number would appear as just one). If you are dealing with Zip Codes, keep this in mind.
Byte is a small whole number.
Integer is a whole number where the 16, 32, and 64 are the size of the number (bigger numbers need more space).
Fixed decimal is typically used for currency, however, you want to watch out for numbers you will use in calculations and later roll up.
Float and Double are the most precise numbers, however, they are not good for comparison where you are trying to see if one value directly equals another.
Date & Time Data Types
The Alteryx Date Time format is specific and may require you to do some data manipulation to get your data into the Alteryx format to utilize the datetime functions within a formula tool. The formats are:
Date – YYYY-MM-DD
Date Time: YYYY-MM-DD HH:MM:SS
Sometimes dates can be difficult to work with, so we have some great tools that you can use to get your date fields into the Alteryx format.
Date Time Macro
The DateTime tool transforms date/time data to and from a variety of formats, including both expression-friendly and human readable formats.
@AlexKo Parse Dates Macro
Weighing in at 55 Alteryx tools, this macro is making its way to the top 5 most downloaded macros on the Alteryx Gallery. This macro will take away a lot of those Date Parsing headaches and make working with dates fun again!
Boolean Data Type
Boolean is simply true or false and useful for flagging data.
Conditional Flag, where 1 = true and 0 = false
Spatial Object Data Types
Spatial Objects are spatial objects like centroids and polygons used for mapping.
Alteryx can bring in Spatial Objects from data file types such as ESRI Shapefiles.
However, if you have a Latitude and Longitude in Excel you can use our Create Points tool to create Spatial Objects within Alteryx.
If you want any more information of data types please see our Alteryx Help
You may already be aware of a function that allows you find the difference between two dates. It's the DATETIMEDIFF() function and you can use it in any tool that allows you to write an expression (Action, Condition, Filter, Formula, Generate Rows, Multi Field Formula, Multi Row Formula, Charting, Report Map, Table, Message, Test). The general syntax is DATETIMEDIFF([DateTime1], [DateTime2], 'units') where 'units' can be 'years', 'months', 'days', 'hours', 'minutes' or 'seconds'. But some of these smaller units - specifically, hours, minutes and seconds - can produce unexpected results.
The DATETIMEDIFF() function uses the data type Int32 as it calculates the difference between. In the case of smaller units of time - hours, minutes and seconds - the data is converted to seconds behind the scenes before it is converted to it's final time unit. A difference between dates of 69 years gets converted into 2,177,474,400 seconds, which is too large for an Int32 data type. Data gets truncated and "wrapped around" to a variety of incorrect conversions, including negative numbers.
A recent case involving a DATETIMEDIFF() expression was attempting to determine the difference between date of birth and a date with the last year and the desired units was in hours. Notice the negative hours in the last two rows.
To get around this problem, hours can be calculated first by calculating age in days, then muliply by 24: DATETIMEDIFF("2013-12-31",[CUST_DOB],"days")*24
Use a similar stategy when calculating minutes and seconds. Attached is an example that illustrates this concept. This example has the added benefit of showing how to get date fields into the correct format for the DATETIMEDIFF() function to work correctly.
Note: example was constructed using v10.1 of Alteryx Designer.