cancel
Showing results for
Did you mean:

# Alteryx Knowledge Base

Definitive answers from Designer experts.
###### Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

## Create a Fiscal Year Calendar

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.   Simple Calendar 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     That's it!   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.)

## How to Find the Next Specified Day from a List of Dates

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!

## How To: Modify File Name for Multi-Tab Excel Workbook

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 To: Calculate the week number, quarter, month start, month end

Alteryx has the ability to calculate the quarter, week number, etc:   Example  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   Input:   Output:   Visit our help documentation for detailed information about Date Time Functions.

## DATETIMEDIFF in 'hours' produces a negative numbers

You may already be aware of a function that allows you to 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 its 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 multiply by 24:  DATETIMEDIFF("2013-12-31",[CUST_DOB],"days")*24   Use a similar strategy 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.

## How To: Create a new date field based on a Formula

How to: Create a new date field based on a formula?     For example, let's say I want to calculate one month prior to an existing date.  i.e. 6/1/15, month prior is 5/1/15.   To do this, I'll add a Formula tool to the canvas, connect it to your data stream, and use the DateTimeAdd() function.   Expression:   DateTimeAdd([Date], -1, 'month')

## How To: Combine Date Ranges with a Macro

How To: Combine Date Ranges with a Macro This module (and embedded batch macro) will provide a comprehensive timeline or date range(s) using multiple, overlapping date ranges.       The macro converts date ranges into spatial objects in order to use the spatial functions in Alteryx to group overlapping or adjacent ranges. This ensures that ranges A and C get are grouped together when A and C do not overlap but both A and C overlap range B (and so forth for larger chains of ranges).           It also allows for “jumping” a user-determined number of days in order to combine regions that do not overlap but are within a specified number of days of one another.

## Alteryx for Excel Users: Find the future date after 'x' working days from [Date]

Finding the future date after 30 from today is easy in Excel.  Use the =TODAY function and add 30.       There's an Alteryx function - DateTimeAdd - that does the same thing:         What if we wanted to know the date 30 working/business days from today?  Excel has a function called 'WORKDAY' to do that.     And it isn't limited to just today's date.  You can use any date.     Finding a future date 'x' working days from today - or any day - in Alteryx is a little trickier.  At least it was.  Attached is an app you can use to pick a date and the number of working days in the future.         From the snapshot above, you'll see the Calendar macro is used.  There are two versions of this macro.  One where the week begins on Sunday and the other where the week begins on Monday.  In the attached app uses the macro where the week begins Sunday but the macro starting on Monday can easily replace it.

## Building A Macro to Calculate Week of the Year

Macros Save Time    One of the best time savings tactics for a developer is to create their own macros for repetitive processes.  In the case of this macro, an Alteryx user may need to know what week of the year an event falls on.  For example, how many transactions occurred during the 23rd week of the year? Accomplishing this task requires formula tools, time/date fields, and come clever parsing to account for leap years.   The first step is to separate the date into its component parts.  By separating the date into its component parts, a calculation can be done later in a formula tool that will create a date for January 1st of the year of that specific record.           The next step is to use a Formula tool to divide the [Month] and [Day] fields by themselves, to create a 1/1 date.  This date will be compared against the record date to determine how many weeks it has been since the start of the year.                 The final step requires several formulas in one Formula Tool to complete the process. Doing math using the Date formula calculations in a Formula tool requires a specific formatting.  The first two formulas determine whether the month is a two digit month or a single digit month. The following two formulas detect whether or not a leading 0 is necessary for proper date formatting and inserts it. The fifth formula brings the components together to create the properly formatted yyyy-01-01 date to compare the record date to.  The sixth and final formula determines how many days have passed from January 1st to the record date, divides by 7, and rounds up.  The result is the week of the year that the record fell on.                 There we have it!  Please find the formulas and configurations in the attached workflow.

## Marquee Crew's Guide to Dates

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.

## Alteryx Data Types 101

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!

## Tool Mastery | Date Time Now

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.

## How to Join on a Date Range

A frequent use case we get asked about is how to join two data sets on a date range where one data set has a start date field and an end date field and the other dataset has a just a date field.