5 Replies Latest reply on Jun 20, 2018 9:32 AM by Jeremy Dempsey

# Date Selection Parameter

I'm trying to create a parameter to select only these relative dates:  Today, Yesterday, Week to Date, and Month to Date

I created the parameter control called [Date Selection], and I created a Calculated Field called [Show Date by Calc]  using this logic:

IF [Date Selection] = 'Today' and  [CallDate] = TODAY() THEN [CallDate]

ELSEIF [Date Selection] = 'Yesterday' and [CallDate] = DATEADD('day',-1,TODAY()) THEN [CallDate]

ELSEIF [Date Selection] = 'Week to Date' and

DATETRUNC('week', [CallDate]-1)+1 <= TODAY()

AND DATEDIFF('week',DATETRUNC('week', [CallDate]-1)+1, TODAY())= 0 THEN [CallDate]

ELSEIF [Date Selection] = 'Month to Date' and

DATETRUNC('month', [CallDate]-1)+1 <= TODAY()

AND DATEDIFF('month',DATETRUNC('month', [CallDate]-1)+1, TODAY())= 0 THEN [CallDate]

END

However, when I drag the [Show Date by Calc] to the Filters card, the parameter is not working.  What am I doing wrong?  Please see attached for my packaged workbook.

• ###### 1. Re: Date Selection Parameter

Hello Jeremy

this is what we got trying to open the file

try posting it again

thanks

Jim

• ###### 2. Re: Date Selection Parameter

Thanks Jim, please try the attachment again.  I made a small correction so you can open it.

• ###### 3. Re: Date Selection Parameter

Hi

this is what your formula is returning

it is only picking up the end date - the way you constructed the last 2 clauses mixes datetrunc and datediff

I am just not a fan of datediff -I would try rewriting the clauses using datetrunc in each

Jim

• ###### 4. Re: Date Selection Parameter

Jim,

I commented out the last two clauses, then I tested the parameter by selecting Today or Yesterday, and I should see the Service Level change, but there's no change.  There must be something else besides the DateDiff and DateTrunc combination in my calculated field.   There's either something wrong with my [Show Date by Calc] calculated field or the way I'm dragging it into the Filters card,  but I'm not sure what I'm doing wrong.

• ###### 5. Re: Date Selection Parameter

I figured it out! Here were the issues:

1) There was a problem with my [Show Date by Calc] Calculated Field.  I had to change my [CallDate] field to a date without time for comparison.  Also, thanks Jim for pointing me in the right direction about the Week to Date and Month to Date calculations.  I got rid of the
DATETRUNC functions, and instead used only DATEDIFF functions.  I changed it to this.

IF [Parameters].[Date Selection] = 'Today' and  date([CallDate]) = TODAY() THEN [CallDate]

ELSEIF [Parameters].[Date Selection] = 'Yesterday' and date([CallDate]) = DATEADD('day',-1,TODAY()) THEN [CallDate]

ELSEIF [Parameters].[Date Selection] = 'Week to Date' and DATE([CallDate]) <= TODAY()

AND DATEDIFF('week',[CallDate],Today())= 0 THEN [CallDate]

ELSEIF [Parameters].[Date Selection] = 'Month to Date' and DATE([CallDate]) <= TODAY()

AND DATEDIFF('month',[CallDate],Today())= 0 THEN [CallDate]

END

2)  Also, one more key issue was the way I had to set up my filter when I dragged [Show Date by Calc] to the Filters shelf, I selected Relative Date

...then on the Special section, I selected Non-null dates

After much searching for an answer, I found there are many different ways to set up a parameter like this.   I think my solution above is cleaner than other solutions I tried.  Here is another way to do it, credit goes to the Evolving Analytics blog, which I tweaked to come up this below:

1)  Created Parameter with list of values:  Today, Yesterday, Week to Date, and Month to Date

2) Created separate calculated fields for each parameter value, for example:

Today = IF date([CallDate]) = TODAY() THEN [CallDate] END

Month to Date =

IF date([CallDate]) <= TODAY()

AND DATEDIFF('year',[CallDate],Today())= 0 THEN [CallDate] END

3) Created Calculated field called [Date Selection] =

IF [Parameters].[Date Selection] = 'Today' THEN [Today]

ELSEIF [Parameters].[Date Selection] = 'Yesterday' Then [Yesterday]

ELSEIF [Parameters].[Date Selection] = 'Week to Date' Then [Week to Date]

ELSEIF [Parameters].[Date Selection] = 'Month to Date' Then [Month to Date]

END

4) Dragged [Date Selection] Calculated Field to Filters shelf. Choose Relative dates as the filter type, then on the Special section, select Non-null dates

Attached is the packaged workbook with the solution.