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).
Solved! Go to Solution.
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 ?
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
I'm using Alteryx 10.5, hence I do not have Datetimeday function in alteryx 10.5
Start Date is a column in my date and I've to select max of StartDate
You can replace DateTimeDay([new start date]) with
tonumber(right([new start date],Length([new start date])-2))
Sorry, I meant tonumber(right([new start date],2))