DATE FUNCTIONALITY and FILTER

[TheBIguy]

Hello All,

Hope you all doing well.

I have two columns one runs with regular date while another one has month end date now I have to do filteration on these dates." DD/MM/YYYY Format"

EVENT_DATE	RATE_DATE
1/7/2021	31/01/2021
2/7/2021	28/02/2021
3/7/2021	31/03/2021
4/7/2021	30/05/2021
5/7/2021	30/06/2021

I have to create 4 filters

Daily
1. So the user will enter a date through action filter on Daily Date ex 20/07/2021 now the first filter is daily so need data for previous day from this date i.e 19/07/2021

MTD

2 Now the Date is fixed i.e 20/07/2021 so the date should pick up from last month end date from End_Date i.e 30/06/2021

QTD

3.as date is 20/07/2021 the last quarter end date would be 30/06/2021 from End_Date

YTD

4. as date is 20/07/2021 the YTD would be 31/12/2020 from End_Date

Help with this people I know we can get this!

Kind Regards!

[Jean-Balteryx]

Hi @TheBIguy ,

What is the part you are having trouble with ? It seems you have all your logic here !

[TheBIguy]

it all has to be dynamic how can we take the max date we have in the data set and create filters based on that date I am having issue with designing custom filter with action tool here!

[apathetichell · Accepted solution]

You need all of your dates as alteryx dates which are in the YYYY-mm-dd format...

Right now you have strings - when the user enters a date it will be in YYYY-mm-dd format and you will not be able to work with it. Do you need help developing formulas for month end/quarter end/etc?

[TheBIguy]

let say user is entering a date on DATE1 column 20/07/2021 now the custom filter should work on Daily_DATE and End_date based on this input

[apathetichell]

@TheBIguy  Hi - I'm sorry but I don't get what you are saying - the date interface tool will provide "2021-07-20". That will not work with your data. This is not an abstract possibility - You will not be able to perform a date comparison between a user entered date and your data.

[TheBIguy]

I am expecting every filtration based on max date from the columns given above

I need something like DateTimeAdd(max([Daily_Date]),-1,"days")

So that above will find the max date from the Start_Date column and then reduce 1 day from that

@apathetichell @Jean-Balteryx

[TheBIguy]

I am expecting every filtration based on max date from the columns given above

I need something like DateTimeAdd(max([Daily_Date]),-1,"days")

So that above will find the max date from the Start_Date column and then reduce 1 day from that

[TheBIguy]

@apathetichell @Jean-Balteryx 

[Jean-Balteryx]

To be able to perform date operation you need to transform your fields a date fields using either a formula tool with DateTimeParse function or the DateTime tool.