Dynamic date filter in input query?

[Lisa_M]

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

[LordNeilLord]

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]

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]

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 · Accepted solution]

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]

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