4 Replies Latest reply on Jun 24, 2017 9:39 AM by nexgen

Difference of two summary fields from previous row

I want to create a report where it will show the difference between two summary fields.

For example:

The summary field is SumTotal

What I want to do is show the difference between SumTotal of current row and previous row. How can I do that?

• 1. Re: Difference of two summary fields from previous row

Since a summary field computes a total for a found set, why would the previous row have a different value?

is each row a sub summary part?

a field from a related table?

a running total?

• 2. Re: Difference of two summary fields from previous row

I think an example will make it clear. Below is an example I have created in excel:

Date and Value is the data in table

The right side 2 columns is the report I want.

Notice the summary called: Report period: 6/11/17 to 6/17/17. They have totals. In the next summary, I want the difference from previous report period which is: 451-303 = 148

• 3. Re: Difference of two summary fields from previous row

Then you want the subtotal for the previous group of records.

Add a calculation field named cSunTotal with this expression:

getSummary ( summaryField ; breakField )

You'll need a break field that has the same value for every record in a group to sort on to get both this kind of summary report and the above function to work. You might use: DateField - DayOfWeek ( DateField ) + 1

then this calculation:

Let ( [ R = Get ( recordNumber ) ;

Previous = If ( R > 1 ; GetNthRecord ( cSubTotal ; R - 1 )

] ;

cSubTotal - Previous

)

If you put this second field in a sub summary part "when sorted by" your break field, it should show the value that you want.

• 4. Re: Difference of two summary fields from previous row

Thanks philmodjunk.