3 Replies Latest reply on Sep 12, 2017 6:57 AM by TODD BENSON

    Date Range as Parameter/Filter

    Quan Tran

      I have a date and time field called alarm_date_time.  I would like to use this date to create a start_date_time and end_date_time (either as a parameter or filter).  My report will change when a user changes the start_date_time and end_date_time.  The data will have the alarm_date_time in the period between start_date_time and end_date_time.

       

      For example, I have the following alarm_date_time:'1/1/2014 12:00' and '2/1/2014 12:00'

       

      If the start_date_time selected by the user after the report is published is '12/30/2013 12:00' and end_date_time selected by the user is '1/30/2014 12:00', then the record containing '1/1/2014 12:00' will be in the report.  The alarm_date_time containing '2/1/2014 12:00' will not be in the report.

       

      Thank you in advance for your help.

        • 1. Re: Date Range as Parameter/Filter
          Sankarmagesh Rajan

          Hi Quan,

           

          Try this

          Creating a Date Range Filter That Automatically Updates

           

          Step 1: Create Start and End Date Parameters

          1. Right-click in the Data window and then select Create Parameter.
          2. In the Create Parameter dialog box, do the following, and then click OK:
            • Name the parameter. In the example workbook, the parameter is named Start date.
            • For Data Type, select Date.
            • For Allowable values, select All.
          3. Right-click the parameter and then select Show Parameter Control.
          4. Repeat steps 1-3 to create an End date parameter.

          Step 2: Create a Parameter to Control a Filter

          1. Right-click in the Data window and then select Create Parameter.
          2. In the Create Parameter dialog box, do the following, and then click OK:
            • Name the parameter. In the example workbook, the parameter is named Filter ON/OFF.
            • For Data Type, select String.
            • For Allowable values, select List.
            • In List of values, add ON and OFF.
          3. Right-click the parameter and then select Show Parameter Control.

          Step 3: Create a Calculated Field to Use as a Filter

          1. Select Analysis > Create Calculated Field.
          2. In the Calculated Field dialog box that opens, do the following and then click OK:
            • Name the calculated field. In the example workbook, the calculated field is named Filtered date.
            • In the formula field, create a calculated field similar to the following:
              IF [Filter ON/OFF]="OFF" THEN [date] ELSEIF [date]<[End date] AND [date]>[Start date] THEN [date] END
          3. Drag the newly created calculated field from the Dimensions pane to the Filter shelf.
          4. In the Filter dialog box that opens, select Months, and then click Next.
          5. Select Exclude and Null, and then click OK.

          Step 4: Create the View

          1. Drag Filtered date to Columns.
          2. Drag Filtered date to Columns again, right-click Filtered date on Columns, and then select Month.
          3. Drag amount to Rows.
          4. Drag type to Color.
          5. For Marks, select Bar.

          Thanks

          sankar

          8 of 8 people found this helpful
          • 2. Re: Date Range as Parameter/Filter
            Russel Quan

            Sankar,

            Thanks for this. 

            It is very helpful.  I like the way you wrote out the instructions, too!

             

            Best Regards.

            Russel Quan

            1 of 1 people found this helpful
            • 3. Re: Date Range as Parameter/Filter
              TODD BENSON

              Fantastic answer!

              1 of 1 people found this helpful