Filter data for previous day

[mercurial_maverick]

Hello all,

I'm have existing alteryx workflow which publishes output to an excel file. Currently there is data in the excel file from previous runs. I need to add in a filter tool for the field "date" to filter for only previous day's data and append to the excel file. For example, tomorrow when I run the workflow it filter for date = previous day and append previous day's data to the output file. any idea how to achieve this using the filter tool?

[JosephSerpis]

Hi @mercurial_maverick I mocked up a workflow let me know what you think?

[AshleyL]

Hi @mercurial_maverick!

You have a few options here! The easiest way to filter to just previous day's data is probably using a formula building block to get yesterday's date and then filter it from there. You can then go on to appending the excels:

Formula: DateTimeAdd(DateTimeToday(),-1,'day')

**Updated to attach workflow

Hope this helps!

Ashley

[mercurial_maverick]

Hello @AshleyL 

Thank you for your response! I tried this and resulted in empty file.

Regards,

Ram

[mercurial_maverick]

@JosephSerpis 

Thank you for your response. Unable to open since mine hasn't been upgraded to latest version yet.

Regards,

Ram

[JosephSerpis]

Hi @mercurial_maverick The Alteryx package I used in my original post would of unzipped in the location you downloaded/saved and you should be able open to up the workflow in the folder it unzipped if you try find again via designer. However for ease I've attached the workflow and excel file I used.

[mercurial_maverick]

@JosephSerpis 

[tm] is the actual time stamp column coming from the database. My [Date] column is formula based = DateTimeFormat(DateTimeParse([tm],"%Y/%m/%d"),"%m/%d/%Y")

I actual used your formula in the filter tool and still getting empty rows

[JosephSerpis · Accepted solution]

Hi @mercurial_maverick that is probably because the date formats are not the same can you try this syntax in the formula tool. Datetimeformat(Datetimeadd(DateTimeToday(),-1,"Day"),"%Y-%m-%d")=DateTimeFormat(DateTimeParse([tm],"%Y/%m/%d"),"%Y-%m-%d") . If that does not work I suggest create two new columns in a formula tool one with the date column with your database. The second column with the previous date you want to filter, you can use the syntax in the filter tool from my previous post to create that then use those columns in the filter tool. This should help troubleshooting and ensure you have no issue with different date formats.

[JosephSerpis]

I mocked up a workflow to show this approach.

[mercurial_maverick]

@JosephSerpis 

[Date] = Datetimeformat(Datetimeadd(DateTimeToday(),-1,"Day"),"%m/%d/%Y")

^^ above formula in the filter tool did the trick. Thank you for working with me on this and appreciate your help!