How to filter rows of current month

prabhakaran808
Level 2
How to filter rows of current month

Hi 

I am a beginner. I want to use the prepare recipe to filter the "Data Period" Column. My expectation is to filter only the current month data. Here i am expecting the output to show only the 3rd and 4th row. Please enlighten me on how to achieve this.

NameCountryOrder IDData PeriodReporting Period
JacobUS34123430-Sep-202130-Oct-2021
JohnUK12341330-Oct-202130-Nov-2021
MattIN23423430-Nov-202130-Dec-2021
ScottIN45342330-Nov-2021 30-Dec-2021
Operating system used: Windows
0 Kudos
4 Replies
emate
Level 5

Hi @prabhakaran808 

If you want to do this kind of manually, I think you can just simply use "Filter rows/cells with formula" for example - but this will require to change current month every time this month change.

If you want to automate this process you could use "max date" column if you have one, or you can create it yourself, for example starting with groupby or maybe creating index column that will help you identify what is the current month (having max_date or fixed index where for example most up do date month will get 0 for example, will allow you again to filter /remove rows that are not matching with max date)

Hope this helps,

Mateusz

0 Kudos
prabhakaran808
Level 2
Author

Hi Mateusz,

Thanks for the response. May you please help to elaborate how to create max_date column to identify the current month. Appreciate if you could share some screenshots of possible for better understanding. My apologies if i am asking a laymen question.

Thanks

0 Kudos
emate
Level 5

Hi again,

To be honest, I am sure I've done this earlier already much easier, but I forgot how and I can't look it up.

Anyway could you try Window Recipe ? you can use "Rank" option, the only thing is that you might be forced to change your date format.

Mateusz

0 Kudos
tgb417

@prabhakaran808 

Welcome to the Dataiku community.

To add to what @emate has said.  Here are a few more thought that might help with your question.

First dates can be hard.  I’ve often been known to say that dates are the “Bane of my existence” as an analyst.

It sounds like you want to filter your data on a date.  The first thing that I would do is make sure that the date column you want to filter on is “parsed” to a format that Dataiku dss can use to do date calculations against.  The format of dates you are showing in your example are not in that format. You do this format change with date parsing visual step.

Once the dates are parsed there are lots of things you can do to filter.  In this case I might create a formula to build a new column that indicates if the date is in the current month or not.  And then add a step to filter on this new column.  I’d then as a next step likely delete the column. 

I’d also look at the date functions to see if there is a function that will do this in one step. (I’m not at a workstation at the moment to check.)

Here is some basic information on using dates with dss 

https://doc.dataiku.com/dss/latest/preparation/processors/filter-on-date.html

Let us know how you are getting on with this.  And welcome to the Dataiku community. 

https://knowledge.dataiku.com/latest/courses/resources/excel-to-dss/dates.html 

--Tom
0 Kudos