Filter Date field on Month/Year only

Hello Community. I'm using In-DB tools today, connecting to a server.

I have a standard SQL date field. I want to filter on the month and year only. I know that I can use the Filter tool and enter a BETWEEN date range, however, I need to enter each month range for an entire year.

"DEMAND_DATE" BETWEEN '2016-02-01'
AND
'2016-02-28'

I do not want to have to enter 12 filters for each month of each year and make sure that I have the correct number of days in each month. I get an error when trying to look for '2016-02-01' AND '2016-02-31' because the computer is smart enough to know that there are not 31 days in February

Also, I need to do this for 3 years in one workflow, that's 36 filters!

Am I missing a simple step to either do a fuzzy filter or something?

THANKS!

Fuzzy Match

In Database

In-Database

Accepted answers

LordNeilLord

Hey @dsemitekol

Ok you're on the right lines.....to make this super simple rather than building loads of filters....Pull the Month and Year back in your query and then in the summarize tool...group by Month, Year, Customer ID/Customer Name and return the Min_Visit Date. This will give you the first instance of each month and year for each customer.

I've mocked up and example (not InDB but the principle is exactly the same).

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

MinDate.yxzp

All comments

LordNeilLord

Hey @dsemitekol

Why do you need to bring one month at a time?

Can you not filter on the entire 3 year period and then use subsequent tools to process each month/year?

Neil

LordNeilLord

If you want to just filter on month/year check out the datepart function in SQL... https://www.w3schools.com/sql/func_sqlserver_datepart.asp

You can do something like...datepart('month', demand_date) between 1 and 12 And datepart('year', demand_date) = 2018

I'm not a sql expert so this is just a guide

Neil

dsemitekol

Hi @LordNeilLord Thanks for the reply. I have 3 years of sales data at the customer level. The customer can repeat during a month. I want to look at only their first visit during each month for each year. For example: Bob shops in March on the 5th and the 21st. I only want Bob's March 5th visit. Bob shops again in April. I want to see Bob's visit in April. After thinking about it, I am not sure if parsing out the month/year is going to help my workflow. I think I am going to go the original route: filter the dataset by each month. Perform my "first instance" using the summarize tool and then rejoin all the data at the end of the workflow with one giant Union tool. It is going to cause 36 filters, summarize, joins, and select tools but I can not think of a better solution.

Capture.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703