1 Reply Latest reply on Dec 20, 2016 12:37 PM by Madeleine Corneli

    Create a calculated field with respect to date

    Aaron Mosharraf

      i have a data source /table as below

       

      Vehicle_ID | Driver | Odometer| reading_date

      ---------------------------------------------

      12345       | John    | 30000      |12/15/2016

      12345       | John    | 25000      |12/01/2016

      12345       | John    | 23000      |11/5/2016

      99999       | David   | 50000      |12/15/2016

      99999       | David   | 35000      |12/01/2016

      99999       | David   | 22000      |11/5/2016

      .                | .           | .               |.

      .                | .           | .               |.

      .                | .           | .               |.

       

      I would like to create a bar chart that would show a usage of each vehicle during a certain date range. The first challenge is to calculate the Odometer difference (delta) within the date period and that would be usage on the Y axis and vehicles 12345 and

      99999 would be on the x axis. I am hoping that I could use a slider to be able to define the date range. My question is how to approach this? I tried to come up with a calculated field but I could not figure it out. Any help will be appreciated.

        • 1. Re: Create a calculated field with respect to date
          Madeleine Corneli

          Hello Aaron,

           

          You should be able to accomplish this using a combination of parameters and calculated fields.

           

          The first step is to create two parameters, one that corresponds to the Start Date of the date range and one that corresponds to the End Date of the date range.  When you create each of these Parameters, make them a Date list and then Add from the Reading Date field.  You can adjust the display of these parameters so that they are sliders or drop down menus.

           

          The next step is to create calculations that pull out the Odometer readings at the Start Date and End Date:

           

          IF [Reading Date] = [Start Date] THEN [Odometer] END

          * duplicate and use [End Date] for the second calculation

           

          Finally, create the Delta calculation to compare these two values:

           

          SUM([End Date Odometer]) - SUM([Start Date Odometer])

           

          I've attached a workbook that demonstrates how this solution can be implemented.  The correct Delta value is calculated at the Vehicle ID level, this means that bringing additional fields onto the view (ex: Reading Date) could affect the values.  The bar chart in the attached workbook reflects the correct values.  This solution requires two sliders because the starting and ending date must be independent variables that correspond to separate calculated fields.

           

          Hope this helps!