1 Reply Latest reply on Jul 15, 2015 5:59 PM by Esther Aller

    Running total by dimension to end at the same point

    Andrew Buttigieg

      I have a simple running total table calculation on a line graph, then added a Dimension to the Color section of the Marks Card. This gave me this:

      RunningTotalActual.PNG

      But I would like both "Progressive Status" series to complete at the end of the graph, so it appears something like this:

      RunningTotalTarget.PNG

      My Data looks a little like this (the Progressive Status Count is simply the record count)

      Progressive DateProgressive Status
      2015-05-21Progressive Created
      2015-05-21Progressive Created
      2015-05-21Progressive Created
      2015-05-21Progressive Created
      2015-05-21Progressive Created
      2015-10-09Progressive Should be Closed
      2015-10-09Progressive Should be Closed
      2015-10-09Progressive Should be Closed
      2015-10-09Progressive Should be Closed
      2015-09-30Progressive Should be Closed
      2015-09-30Progressive Should be Closed

       

      Any help would be appreciated.

        • 1. Re: Running total by dimension to end at the same point
          Esther Aller

          Hi Andrew,

           

          The view can be achieved by creating a calculated field that finds the running sum for just Progressive Created, and then adding the calculation to the view as a dual axis:

          1. Select Analysis > Create Calculated Field
          2. In the Calculated Field dialog box that opens, do the following, and then click OK:
            1. Name the calculated field. In this example, the calculated field is named "Running total of Progress Created"
            2. In the formula field, create a calculation similar to the following:

              RUNNING_SUM( COUNT(
                 IF [Progressive Status] = "Progressive Created"
              THEN [Progressive Date]
              END
              ))

          3. Drag [Running total of Progress Created] to the Rows shelf
          4. On the marks card for Running total of Progress Created, remove [Progressive Status] from Color
          5. Right-click on [Running total of Progress Created] on the Rows shelf and select Dual Axis
          6. Right-click on the right-hand axis in the view and select Synchronize Axis

           

          Note, you could also create a second calculation that returns the running sum of count of dates for Progressive Should be Closed and graph both calculations on the same axis.

           

          I hope this answers your question!