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

    Date Selection Parameter

    Jeremy Dempsey

      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
          Jim Dehner

          Hello Jeremy

          this is what we got trying to open the file

          try posting it again

          thanks

          Jim

          • 2. Re: Date Selection Parameter
            Jeremy Dempsey

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

            • 3. Re: Date Selection Parameter
              Jim Dehner

              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
                Jeremy Dempsey

                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. 

                Parameter control 2.png

                • 5. Re: Date Selection Parameter
                  Jeremy Dempsey

                  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

                  Parameter control 3.png

                   

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

                   

                  Parameter control 4.png

                   

                   

                  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.