WeeklyAlteryxTips#60　How to count date

In this article, I would explain the way to count days between start date and end date.

For example, supposed to want to know how long the delivery period have taken for knowing the customer satisfaction. For that, there are several ways to achieve.

The delivery period is that the order date is the start and ShippingDate is end date as follows.

Considerable way are as follows.

1. Simple date count
2. Count date excluding for weekend
3. In addition to #2, count days you exclude from a specific list, such as your company’s holiday calendar.

Let’s see those ways!

1. Simple date count

In this case, you can only use function “DateTimeDiff”.

DateTimeDiff([ShippingDate],[OrderDate],"day")+1

The order of the column is that the 1st is newer date and 2nd is older date. Please be careful for specifying the column order in reverse, the result would be negative number. The sample result is as follows. The DateTimeDiff function doesn’t count the start date (or end date), so the answer will be the number of days including both(start and end) minus 1 day. If you want to contain both day, please add one. In this case, I added one.

2. Count date excluding for weekend

When you count date except for the weekends, you may do it by using function, too. But it is difficult to create the formulas. Generally speaking, you would use some tool to create the logic, but this can take a long time if there are too many differences between the dates.

The workflow is as follows.

The important tool is the Generate Rows tool.

At this tool, create the new field as Date type and the Initialization Expression is [OrderDate] and Condition Expression is “Date <= [ShippingDate]”. And you have to create the dates which are between OrderDate and ShippingDate, so you have to create the function below in the Loop Expression.

DateTimeAdd([Date],1,"Day")

From that, the records are created dates that are between the OrderDate and ShippingDate. The result is as follows(Only first 8 rows are shown).

And then, you have to only count the dates except for Saturdays and Sundays. Getting the day of week can be easily done with the DateTime tool.

In this tool, select the “Date/Time format to string” because you have to use the DateTime type field. And select “English” at the language setting, select the Select “Custome” at the the “format for the new column” and set “dy” at the “Specify a custome format for the new column”. You can also use “%a” instead of “dy”.

Next, on the Filter tool, you can use the custom filter below.

!([DayOfWeek] IN ("Sat","Sun"))

And then, use the Summarize tool to count dates.

The last Sort tool in the workflow is for sorting the data because the Designer with AMP Engine changes the record order.

3. In addition to #2, count days you exclude from a specific list, such as your company’s holiday calendar.

Actually, many cases are mostly like this. Generally speaking, there are some national holidays in many countries and companies have also specific holidays. Only this 3rd workflow can be responsible such all holiday patterns.

This workflow needs the holiday list. In organizations where holidays are fixed on certain days of the week, these can be removed mechanically, but in organizations where holidays are fixed on certain days of the week, all days off must be listed.

For sample workflow, I created the small holiday list as follows. In this sample, the date on this list is considered as holiday. And also the Sundays and Saturdays are considered as holiday. The days of week can be gotten by the DateTime tool, so I use it and I consider them as holiday.

The workflow is as follows.

This workflow is based on #2, but I added the Join tool after the Filter tool.

The joined records are holidays that is in the Holiday list. So, you can use the L output on the Join tool as business day. Now all you have to do is count them. Finally, you can obtain the data below.

Conclusion

• I have explained the way to count date which are between 2 specific date.
• I have explained 3 ways.
  • Simple date count
  • Count date excluding for weekend
  • Count date by the list of the holidays

This way can be used for the hourly data, but when the units become smaller, the data volume will be large, so the workflow running time could be taken much more time.

Sample Workflow Download

The next blog is …

Next post would be about the way to save some data stream which have different schema to the multiple worksheet.