Dynamic Date Filter in Data Prep 3. O Aka Data Recipe in Tableau CRM(Einstein Analytics)
In Dataflow we would have the option to apply the dynamic date filters/custom filters in the filter node by using SAQL syntax. e.g, date(‘created_Date_year’, ‘created_Date_Month’, ‘created_Date_Day’) in [“1 year ago”.. “current year”]
But when you are in a situation to filter rows on the dynamic date range in Data prep, say asset created less than a year. unfortunately as of today, we cannot be able to use TCRM SQL filters in the filter node of data prep. then how to do it….? no worries this blog is an attempt to answer these questions and show you some use cases.
solution overview:-
In the Transform Node you have to create a field with relative dates or buckets on the dates or flagged fields based on date fields and then use those fields in the Filter Node. in that way our filter node will be dynamic always.
Business Use Case 1(numbers- Dynamic Date Filter):-
let's assume you have a business requirement to include Rolling 15 months opportunity data only for the calculation/dataset creation.
Step 1:- create months between the current day and opportunity-created date in the Transform node.
Step 2: now you will have the opportunity to Age in months columns. create a filter based on calculated columns(opportunity to Age in months). in this way, our filter will be dynamic always.
please refer below videos
Business Use Case 2( flag-dynamic date filter) :-
let’s assume you have a business requirement to calculate the last call Date of the Event of Each Account.
Business Logic Consideration: you have to filter out the future event records in order to calculate each Account's Last call Date. you could achieve this by creating a flag based on date fields.
Step 1:- Mark with each records whether they are past events or future events in the transform Node.
Step 2: now you will have each event record flagged with past events(‘Y’) or future events(‘N’).
Step 3: filter the past records by flagged columns value(past event = ‘Y’)
please refer below videos
Business Use Case 3( buckect-dynamic date filter) :-
lets assume you have a business requirement to compare the current month and previous month of the Event of Each Account.
Step 1:- Create Event Age in months field in Transform node.
Step 2: create a bucket based on the Event Age in months calculated columns.
Step 3: filter the records by using the bucketing field.
please refer to videos
<iframe src=”https://player.vimeo.com/video/677886582?h=9e4bba1d7d" width=”640" height=”564" frameborder=”0" allow=”autoplay; fullscreen” allowfullscreen></iframe>
