cancel
Showing results for
Did you mean:

# Alteryx Knowledge Base

## 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!

## Alteryx for Excel Users: Getting data for dates

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()':     Results in:     Use the same method if you want just the date or time.   Just date:     Just 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.

## Advanced Join: value from one file is between, > or < a value from another file

It's not uncommon to have a situation where you need to conditionally join one dataset to another.  Perhaps the most common is when you want to join one file to another where a date from the first file is between, greater than or less than a date(s) on a second file.  The default tools found in the Join section of the tool palette don't provide a simple way of doing this (it can be done, but you need to string several tools together to make it work.  There is a better way!  Read on...).    There is great macro available in the public Alteryx gallery called Advanced Join (find it here, but spoiler alert... you can download the attached workflow which includes this macro so you don't have to go to the gallery to get it).  The Advanced Join gives you greater latitude than the Join tool.  Most notably, you can select records from file A that are unique to A AND intersect with file B.  Now you may be thinking, “I can do that by unioning the records from an inner join with records from a left join,” and you would be correct.  But it takes two tools to do what one Advance Join does.  More importantly, the Advanced Join allows you to put a conditional statement on your join which is something you can't do with the Join tool.  And it’s this feature - the ability to use conditional statements in a join - which we will focus on for our purpose here.   Let's get into some examples.  I have a file, 'Fruit List’, which contains data about various fruits.  This file contains a Column Id, a Fruit Name, a Start DateTime and an End DateTime:     I have a second file, 'Greek Alphabet’, which contains a Column Id, a Greek letter and a Datetime.       I want to join the two files on ColumnId where the Datetime from Greek Alphabet (file B) is BETWEEN Start Datetime and End Datetime from Fruit List (file A).  Here's the workflow and a screenshot of how to configure the Advanced Join:         And here are what my results look like:       Only one record from Greek Alphabet matched one from Fruit List on ColumnId where Greek Alphabet's Datetime was between Fruit List's Start Datetime and End Datetime.   In the next example, I have the same Fruit List file and want to join it another file, Greek Alphabet that contains just one datetime filed:       The first thing to note is both files have a field called 'DateTime.'  We'll want to give these unique names to avoid ambiguity when we write our conditional state in the Advance Join configuration.              I want to join both files on ColumnId but only when DateTime from Fruit List is LESS THAN DateTime from Greek Alphabet:         And the results...:       Let's look at one last example.  This time, I'm going to use the Fruit List and Greek Alphabet files used in the first example (Fruit List has a Start DateTime and an End DateTime).  I'm interested in matching records where DateTime from Greek Alphabet is BETWEEN Start Datetime and End DateTime from Fruit List.  I'm not matching on ColumnId this time.     For the Advanced Join configuration, I'm going to cross join my files.  (CAUTION: the resulting join could contain as many rows as the product of the number of rows of the incoming datasets - a.k.a. Cartesian join - depending on how restrictive your conditional is.  This means if you're joining 2 datasets that contain a million records each, the resulting dataset could contain as many as one trillion records! ).  If I had wanted to match on ColumnId, I would have had to do that separately using a Join tool. The cross join option only allows you to apply a conditional statement:       Results from our 3rd example:     Notice how 10 records from Greek Alphabet were joined to just one record from Fruit List.     The Advanced Join tool can save you time and a lot of headaches when you want to join files using a conditional statement.  It has some limitations - you can only join two datasets and include one conditional statement per tool, cross join limitation mentioned above - but Advanced Join provides greater capability and flexibility than the standard Join tool.

## Calendar and Date Aggregation

Date aggregation has been discussed before and macros have been developed to assist with aggregating dates at various levels (e.g. month, week or year). Some of these macros even allow you to find the sum, mean, median or mode dates. See an example here.   Yet I’ve found the data to be incomplete or requiring additional processing to get the data the way I wanted it. Rather than copying and pasting this process from workflow to workflow, I decided to create a macro (and this macro doesn’t require any configuration!) and make it available to a wider audience with the hope that it will save time and energy by eliminating the need to recreate the process of translating a date week number, quarter, etc.   Data Format and Layout:   Essentially, it works like an input file. For all practical purposes, you can think of it as a date table with Date being the primary key.         As long as you have a date in yyyy-mm-dd format in another file, you can match to this calendar table.   Field Definitions & Notes:   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.   Common Use: The attached macro/date table provides a quick and easy way to access date data you would otherwise need to calculate or decipher. Let’s say you have a file with retail transactional data. On the file, you have a date and line-item revenues, but you want to determine how much revenue was generated by week. Ordinarily, you would translate your date into a corresponding week number before you could summarize your revenue data.   Missing Leading Zeros: Numeric fields (specifically, Byte and Int16 from the data format above) will not have a leading zero. In the event you need to add the leading zeros back in (the most common would be Month and Day fields), you need to change the data type to String using a Select tool and then use the following expression in a Formula tool:   PadLeft([FieldName], 2, '0')   This ensures the field has two characters. If it only has one, a zero will be added to the left (e.g. 1 becomes 01).   A workflow illustrating this process is attached. The macro is included in the package.

## 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: Save an Excel File with Today's Date

We get quite a few requests asking how to add the current date to Excel spreadsheet file names. The difference in adding information to your file name in Excel and other output configurations is that you have to use the Reporting tools for Excel to accept the new file name.   Why would I have to use the Reporting tools, you ask? Simply put, Excel thinks that when you are adding a prefix or suffix to the file name that you are stating that additional information is the “Sheet Name” and not appending the file name from the Output Tool. The Render tool in the Reporting tab on your palette basically tricks Excel into thinking that you are creating a separate report and is able to append the date.   In order to add the date to the file name you will connect these tools to your workflow in the order, you see below.      In the Formula Tool, create a new field that is a String type.  Go to the DateTime functions and add the DateTimeToday() expression. No configuration of the expression is necessary.      The Table tool will allow you to Group By this new field. In the GroupBy Configuration, choose the Date field you created in the Formula. In the Per Column Configuration, uncheck the new field. This will allow you to group by the new field name in the Render Tool and still remove it from the data so that your new field does not appear in your report.          Use the Render Tool to output your Excel spreadsheet with today’s date in the file name.   Output Mode - Choose a Specific Output File. Output File - Specify the Excel 2007 Spreadsheet (xlsx) and point to where you would like to save the documents. The file name you specify will be replaced with the date in step 5 below. You will then check the box that says Group Data into Separate Reports. Field to Group on - You will choose your new field that you created. Modify Filename By – Replacing Filename With Group. Report Data – You will choose Table and can leave the rest as defaults.

## 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.

## Use dates to query incoming date from Salesforce

When using the Salesforce Input tool, you can limit the data to a certain timeframe. Alteryx has the ability to use SOQL date queries so that you pull data for a certain time frame instead of pulling ALL of your data and then having to filter out what you are interested in. Pulling all of your data can take some time depending on the size of your table and this is a quick way to get around a potentially slow data pull.   Once you have entered your Salesforce credentials, go ahead and pick your table (1). From the table you have selected choose your fields (2). At least one has to be some kind of field with dates and/or date times recorded. And finally, you can enter in a SOQL query that will execute on your date field (3).   The date query that you enter could be something as simple as a specific date, for example, ‘DateField  = 2016-03-31’ or ‘DateField  > 2016-03-31’. Or you could use some of the SOQL date literal query options. For example ‘DateField  = YESTERDAY’ or ‘DateField  = LAST_QUARTER’         Here is a full listing of your date query options when using SOQL to query on dates in the salesforce input tool - Date Formats and Date Literals. If you need help in getting your salesforce login credentials please see our Obtaining Credentials for the Salesforce Connectors article.   If you run into any issues with the Salesforce input tool, please feel free to reach out to our Client Support team through the Case Portal.

## 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.