Filter data from a dataset

[Shaaz]

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

[DavidP]

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]

[cmcclellan]

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 ?

[patrick_mcauliffe]

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

[Shaaz]

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

[Shaaz]

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

[DavidP]

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

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

[DavidP]

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

[Shaaz · Accepted solution]

Hi All, Thanks for the help.

I've got the logic and developed the workflow.