Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Filter data from a dataset

Shaaz
9 - Comet

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

8 REPLIES 8
DavidP
17 - Castor
17 - Castor

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

cmcclellan
13 - Pulsar

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
14 - Magnetar
14 - Magnetar

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
9 - Comet

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

Shaaz
9 - Comet

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

DavidP
17 - Castor
17 - Castor

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

 

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

DavidP
17 - Castor
17 - Castor

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

Shaaz
9 - Comet

Hi All, Thanks for the help.

 

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

Labels