1 Reply Latest reply on Jul 22, 2016 7:05 PM by Shinichiro Murakami

    Running Total with Start and End Dates

    Grant Klgu

      I am having issues creating a view that shows the total incidents open at a given date. The data has a start and end date, and I am trying to create a line graph to show how many incidents there were over time.

      Name
      Start Date
      End Date
      Incident 18/23/20147/2/2015
      Incident 25/3/20087/30/2015
      Incident 17/16/20135/6/2016
      Incident 34/5/20094/5/2011

      When the date reaches the start date, I want the count of incidents to go up, but when it reaches the end date, I want it to decrease by one.

       

      I've attached a dummy book simply outlining what I am trying to achieve. Any help is welcomed.

      Thanks

        • 1. Re: Running Total with Start and End Dates
          Shinichiro Murakami

          Hi, Grant

          Here is my approach.

           

          First, create "Date list" which consists of both Start and End date.

          Otherwise, Start and Date does not necessarily match and calculation becomes incorrect.

           

          Create calculated fields.

           

          [Start Date]

          if [Pivot field names]="Start Date" then [Name] END

           

          [End Date]

          if [Pivot field names]="End Date" then [Name] END

           

          [Count Start Date]

          COUNT([Start Date])

           

          [Count End Date]

          COUNT([End Date])

           

          [Starrt - End]

          running_sum([Count Start Date])-running_sum([Count End Date])

           

           

          Thanks,

          Shin

           

           

          9.3 attached.