Extracting data between date time dd:mm:yyyy hh:mm:ss

Question

Hi I want to extract data between date time,

i have a huge data file and I need extract daily weekly monthly and yearly, after which calculate the difference between the start and end date excluding weekend.

example : I want data the below extraction

Daily - 11:09:2023 16:00:00 (4pm) to 11:10:2023 05:00:00 ( 5 am)

Weekly - 11:06:2023 16:00:00 to 11:18:2023 05:00:00

i don’t know if this can be done cause I will also need the dates excluding weekends, I run the report on Monday it should know to take Friday 4pm to Saturday 5 am

rzdodson · Answer

@ThanyaWells123 there are a couple of approaches you can take here depending on your use case. In both instances I am suggesting below, I recommend solving this via Analytic App as it'll give you the most flexibility to respond to changing requests (i.e. knowing the number of completed requests within different date ranges).

SQL Query/In-DB Approach:
If you are using In-DB or some other SQL-based query within your Input tool, you can have Date tools (Interface palette) update specific strings within your query, which will then control what data is even brought in to Alteryx for analysis.

Flat File Input:
If your data is some sort of flat file (CSV, or delimited file), you can do the following:

* Create a Text Input tool with your desired start and end dates (this can be arbitrary).
* Attach Date tools to the Text Input tool. Your first Date tool will update Row 1, Column 1. The second Date tool will update Row 1, Column 2.
* Insert an Append tool immediate following your bulk data. Your bulk data will flow in to the "T" input anchor. The Text Input tool you created will flow in to the S input anchor of the append tool. This will "attach" the Start and End date fields to every row in your main data set to create helper columns.
* Insert a Filter tool. This formula syntax will omit any records that fall outside of your desired range.

YourDateField>=[Start Date] and
YourDateField<=[End Date]​

From here, we'll need to mock up Formula syntax that calculates the number of days between the desired start and end times that exclude weekends - this is just a Bool value whereby Saturday and Sunday return 0 and all other days return 0 (Formula 21). Finally, a Summarize tool (Summarize 22) that adds the field's values together should be able to do the trick for you. Full workflow attached.

EDIT: the attached solves the scenario where we are calculating the completed requests that fall inside a particular start and end date/time range and excludes weekends; it does not calculate the number of requests completed within the preceding weekend.

Solution.yxzp

ThanyaWells123 · Answer

Hi thank you! I cannot add the workflow however I can try to some sample data

We have employees completing their requests on an application. We require to know on day to day basics how many requests have been completed. Our team works from 4 pm in the evening to 5 am the next day.

I need to create a workflow which will filter requests completed between yesterday 4pm till today 5 am

when I download the data it gives me all the data completed in the application I required a workflow which will only pull data completed date between previous day 4pm to today’s day 5 am yyyy:mm:dd hh:mm:ss

completed date data is as below

2023:11:14 02:00:00

2023:11:13 17:00:00

I need a filter or formula which will give me data between

2023:11:13 16:00:00 to 2023:11:14 05:00:00

It should be automated in such a way that there is no requirement for changes in alteryx to extract the data,

another scenario is when I run the report on Monday it should automatically take Friday 16:00:00 to Saturday 05:00:00 as our working days are Monday to friday

kindly help me on to go about this

caltang · Answer

As @CoG said, it will be very helpful to have some sample data and a clearer ask from your side. Still a bit blur on what you really want from the date time...