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

Dynamic date filter in input query?

Lisa_M
8 - Asteroid

Hi Chaps

 

I'm building a report to deploy to my user's desktops pending a delivery via OBIEE.  I need to provide them with the last three month's revenue data as part of a trending report.

 

Up until now, I've pulled the data directly from the data warehouse with an input tool, then used a date filter like this:  DateTimeAdd([DateTimeNow], -3, 'month') to get the last three months at the point at which the report is run.  It's not working EXACTLY as I'd like it, because I want it to pull the last three months before the month today is in - so for example, if I ran this today (26/01/2018), I should get Oct 17, Nov 17 and Dec 17.  But a bit of trial and error, and I'll figure that out.

 

The real issue is that it takes AGES to run, because the input tool is pulling ALL of the data, and THEN filtering.  And I have very impatient users (you think they'd be happy I agreed to do this - not fuss about running time, sheesh!).  So what I'm trying to figure out is if I can filter the data as part of the input tool.  I've tried this:

Date_Column >= DATEADD(MONTH, -3, GETDATE()) 

and

DATEDIFF(MONTH, Date_Column, GETDATE()) <= 3

 

But no dice - when I test the query, I get a data wrap OCI error.  Any suggestions how I could do this?  Any help very gratefully received!

 

Lisa

5 REPLIES 5
LordNeilLord
15 - Aurora

Hey Lisa,

 

What database are you connecting to? If it is SQL then I would write that as part of the SQL query

Lisa_M
8 - Asteroid

That's what I was trying to do.  Figured it out in the end, but I'm damned if I can find the workflow I did it in!

ZacharyM
Alteryx Alumni (Retired)

Hey Lisa!

 

Typically the GETDATE function wants a single quote around the unit component of it - that would explain the wrap error.

 

For example, in Snowflake it would look like this;

 

Select * From SHOPIFY.SHOPIFY.ORDERS
WHERE "CLOSED_AT" < DATEADD('month',-3,CURRENT_DATE())

 

What database are you working against and I can help hammer out the syntax more specifically - GETDATE(), TODAY(), and CURRENT_DATE() are examples of the syntax nuances between DB's :)

 

Cheers!

Zak

 

Lisa_M
8 - Asteroid

Hi Zak

 

I figured this out a while back - I went with this in the end:

 

Where To_Date(To_Char(REPORTING_PERIOD), 'YYYYMM') Between Trunc(Add_Months(SysDate, -3), 'MM') And Last_Day(Add_Months(SysDate, -1))

 

This pulls a rolling three complete months of revenue data from the database (Oracle 11g) for my users - and them I'm doing some other interesting stuff where I'm then comparing that to the source of the data and analysing trending, which shows my user where (if) something weird is going on - ie if the revenue is trending down but the source is trending up, etc, etc.

 

Thanks anyway though.

 

Cheers

 

Lisa

Gina2021
8 - Asteroid

Hi Zak,

 

I'm an Alteryx newbie and just got the below to work. I've read soooo many forum questions but can't figure out how to dynamically filter the last two years of recent data in the SQL Editor in the Input Tool.

 

My column LAST_UPDATE_DATETIME datatype is DATETIME and the output looks like: 2020-09-28 21:54:44 (from Oracle)...so I was surprised this worked...where table.LAST_UPDATE_DATETIME >= '30 Jan 2019'

 

1. Do you know how I can make this dynamic?

2. Do you have a syntax cheat sheet to work with GETDATE(), TODAY(), and CURRENT_DATE()? Which databases do these work with?  

 

Thanks so much!

Gina

Labels