4 Replies Latest reply on Nov 25, 2015 12:03 PM by Brian Calvanese

# Calculate Difference between Running-Sums

Hi All,

Hope everyone is doing well.  I am trying to calculate the difference between two running_sum table calcs. My running_sums are open and close dates.  The purpose of this calc is to determine how many data points are open at any given point in time.

This type of question has been well documented.FAQ:  Open & Close Dates   I am trying a different approach here.  If I know how many open dates and how many closed dates have past, I can subtract those two numbers and get the count of how many are still open.  I realize that his limits the information that will be available for each data point, but I just need the number in order the plot the trend over time.

Does anyone have any thoughts on how to solve this?

I appreciate any input.

Thanks!

Paul

• ###### 1. Re: Calculate Difference between Running-Sums

Paul,

I think the best way to achieve this is by pivoting the two date fields you're comparing. I don't know of a way to compare it using the existing data structure, though someone else may chime in with a creative solution.One major caveat is this will restructure your data in a way that is sure to affect aggregations elsewhere, so I'd recommend making this its own data source for this specific purpose.

Here's how that workst:

1) Pivot the two date fields, like this:

2) Build your view and running sum, then perform a secondary calculation on that running sum, as shown here:

The attached workbook has the full example for you to look at.

Robert Rouse

InterWorks, Inc.

1 of 1 people found this helpful
• ###### 2. Re: Calculate Difference between Running-Sums

Hi Robert,

Thanks for the help!  The pivot really helps.  How did you address the null values as not all data points have a closing date?  My graph either starts at a negative number.

The link you provided doesn't seem to have your changes.  Would please attempt to attach it again?

Thanks!

• ###### 3. Re: Calculate Difference between Running-Sums

Sorry about that. I have modified the attachment above. To deal with the closing date being null, you can use a calculated field and put it on the filter shelf. Here's the calculation I used:

[Pivot field names]="Closing Date" AND ISNULL([Pivot field values])

• ###### 4. Re: Calculate Difference between Running-Sums

Check out this recent post on a similar topic...

Delta Difference