community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Filter data from a dataset

Alteryx Partner

Hi,

 

Can you guide me how to reach the output for below scenarios. Assume I've previous 10 years of data in excel file where I need latest 4 years.

 

Case 1:
If StartDate < 15th day of any month (for example - 11.02.2019) then consider the previous month as latest month, I've to consider 4 years of data from the period of 01.02.2015 to 31.01.2019.

 

Case 2:
If the StartDate >= 15 days in a month (for example - 16.02.2019) then consider that month as the latest month, 4 years of data need to consider for an period of 01.03.2015 to 16.02.2019 (only this 16 days need to consider as one month).

Pulsar

This is how I would do it:

 

1. Convert [StartDate] to a date type field

2. Define [period start] and [period end] based on your conditions

3. Use a filter to select dates between [period start] and [period end]

 

date period start and end.png

Alteryx Partner

Where does [StartDate] come from ?

 

So , yes, you can do this, but it would depend on what your data is like .... can you post some sample records ?

Highlighted

What have you tried so far?  

I might be misunderstanding, but it seems that you'd be able to use a few short formulas or series of filters.

It depends on how you want to use it within the workflow and what your data looks like, but here's the general idea I came up with.

 

(all below assume date format of YYYY-MM-dd)

Start by identifying the day of month in a new field:

dayOfMonth  = DateTimeDay(StartDate)

 

Create a condition or new field to use next (or nest the above into this):

LatestMonth = 

If

dayOfMonth <15

then

DateTimeAdd( ToString(DateTimeYear(StartDate)) +  '-' + PadLeft(ToString(DateTimeMonth(StartDate)),2,'0') + '-01' , -1 , 'month')

else

StartDate

endif

 

 



Alteryx Partner

I'm using Alteryx 10.5, hence I do not have Datetimeday function in alteryx 10.5

Alteryx Partner

Start Date is a column in my date and I've to select max of StartDate

Pulsar

You can replace DateTimeDay([new start date]) with

 

tonumber(right([new start date],Length([new start date])-2))

Pulsar

Sorry, I meant tonumber(right([new start date],2))

Alteryx Partner

Hi All, Thanks for the help.

 

I've got the logic and developed the workflow.Capture.JPG

Labels