Date Filter(Relative Dates) as dynamic text

Hi Shaun,

I am using relative Date filter where i am using date range, please refer screenshot

Date%20filter
I choose to view data for previous 3 days and post 3 days now I want to calculate count of cmp 3 days before start_date and count of CMP 3 days after the start_date.
image .

Can I do it in Arcadia.

@rawadhwal

Given that this is anchored to today, we can use conditional SUM-IF expression using now() function to get the counts you are looking for.

We will follow similar sum-if expression used in this how-to article Month over Month comparison using parameters

  1. To get 3 days before and 3 days after relative to today’s date, you want to use 6days as the duration as shown here:
    image

That will automatically filter the data for those 6 days.

  1. In our shelf expressions, we can simply setup a SUM-IF condition that compares the date field to now(). As shown here:

    sum(if([Ordered Date] > now(), 1, 0))
    sum(if([Ordered Date] < now(), 1, 0))  
    

  2. You can then add any dimension column to get the breaks out you want:

@shaun: this was a great help, I need one more customization in Filter Instead of calculating 3 days before and 3 days from NOW(). I want to choose any other operation date and do the calculation.
EX: Current condition 3 days prior to today with respect to [Change Start Date 1].
I am looking for
Filter_1: Date_1
Filter_2: Change Start Date1
I can do cascading filter but will I be able to choose date range as 3 days prior to Date_1 and 3 days after Date_1.

@rawadhwal You want to apply similar technique as: How to manipulate time filter using parameters for time comparisons

So your SUM-IF conditions would look something like:

Assuming you have a date filter that output “Ordered Date” as parameter and you want to anchor based on the start date of the selection:

  • 3 days before:
    sum(if([Ordered Date] between date_sub(cast('<<Ordered Date.start>>' as timestamp), interval 3 days) and '<<Ordered Date.start>>',1,0))

  • 3 days after:
    sum(if([Ordered Date] between '<<Ordered Date.start>>' and date_add(cast('<<Ordered Date.start>>' as timestamp), interval 3 days) ,1,0))

1 Like

@shaun
Thanks for the help, but still I am not able to find solution. Let me describe use case.
Use Case Description:

  1. User wants to select a specific date lets say DT in filter_1(Not Date Range).
  2. Filter 2: User needs a drop down DURATION.

Can you please help me in finding the solution of this use case.

Note: For point no 1 I got solution from one of your post; by using JS.
For Point 2 I tried to find workaround by calculating -3 to +30 days Count(tkt) in hive and brought table to Arcadia connection. In dashboard I tried to put those 54 col in <> filter but for particularly these col’s I am getting error other col are populating properly.
I will post the error and query in separate post.

@rawadhwal You want to use combination of custom filters and parameters to accomplish this.

Example of customer filter here: Can I use list of attributes in filter

Here’s how I would setup the dashboard.

  • Notice the 2 customer filters at the top
  • One filter outputs the anchor date as parameter “DT
  • The other filter outputs “duration” as parameter
  • Then use these two parameters in the filter shelf of the visual (details below)

The expression used in the Filter shelf:

[Order Date] between date_sub(cast('<<DT>>' as timestamp), interval <<duration>> days) and date_add(cast('<<DT>>' as timestamp), interval <<duration>> days)

1 Like