2 Replies Latest reply on Sep 20, 2017 7:17 AM by Neil Lord

# Dynamically choose the date range in X Axis based on the chosen parameter

Hi,

I am dynamically displaying either days/months/weeks in the X Axis as per the chosen parameter as shown below:

WHEN "Daily" THEN DATETRUNC('day', [fulldt])

WHEN "Monthly" THEN DATETRUNC('month', [fulldt])

WHEN "Weekly" THEN DATETRUNC('week', [fulldt])

END

Now, I want to display only the last n days if it's daily, last n months if it's monthly and last n weeks if it's weekly.  I am trying to do something as shown below:

WHEN "Daily" THEN (DATETRUNC('day', [fulldt]) >= dateadd('day',-60,WINDOW_MAX(max([fulldt])))

WHEN "Monthly" THEN (DATETRUNC('month', [fulldt]) >= dateadd('month',-6,WINDOW_MAX(max([fulldt])))

WHEN "Weekly" THEN (DATETRUNC('week', [fulldt]) >= dateadd('week',-15,WINDOW_MAX(max([fulldt])))

END

Thanks,

Sudha

• ###### 1. Re: Dynamically choose the date range in X Axis based on the chosen parameter

Can you make the return of fulldt conditional and base it off today?

WHEN "Daily" THEN DATETRUNC('day', if [fulldt] >= dateadd('day',-60,today())  then [fulldt] end)

WHEN "Monthly" THEN DATETRUNC('month', if [fulldt] >= dateadd('month',-6,today()) then [fulldt] end)

WHEN "Weekly" THEN DATETRUNC('week', if [fulldt] >= dateadd('week',-15,today()) then [fulldt] end)

END

• ###### 2. Re: Dynamically choose the date range in X Axis based on the chosen parameter

Hi Sudha,

You can try using something like this:

iif([Date Display] = "Daily" ,DATEDIFF('day', [Order Date], {Max([Order Date])})<=60,

iif([Date Display] = "week" ,DATEDIFF('day', [Order Date], {Max([Order Date])})<=15,

DATEDIFF('month', [Order Date], {Max([Order Date])})<=6))

I have attached an example workbook for you