Dynamic in-DB filter

[Stormphoenix]

Please help!!

I'm new to both Alteryx and SQL and I'm struggling to write an expression in the in-DB filter tool that brings back only the last 12 months from whatever date the flow is run. 

I know I can bring it out of the database and use a normal filter tool and do it that way, but i'd rather keep the run time down as much as I can.

Any help would be much appreciated, thanks!

[AngelosPachis · Accepted solution]

Hi @Stormphoenix ,

Can you try the following expression in you In-DB filter tool :

"YourDateFieldHere" >=Dateadd(month,-12,GetDate()) 

The getdate function will return today's date and then with the Dateadd function you will only keep records that are within the last 12 months.

Hope that helps,

Angelos

[mbarone]

You can do that with a Dynamic Input tool.  Read through the Tool Mastery Index|Dynamic Input Tool  section.  Basically, you'll create your "filter" on the Alteryx canvass.  And then feed that filter into your SQL that you created for your database.  Write the SQL for a particular time frame, and then you just swap out that time frame with the one you create on the canvas.

[ChrisTX]

This post from yesterday should help:

How to: Dynamic In-DB filtering without database writing permissions

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-Dynamic-In-DB-filtering-without...

Here's a list of all functions:  https://help.alteryx.com/current/designer/functions
Take a look at the date functions.

[Stormphoenix]

@AngelosPachis Brilliant, this worked perfectly, thanks so much!

[Stormphoenix]

@ChrisTX @mbarone  Thank you for the links, I'll definitely have a look through.