I want to create a report where it will show the difference between two summary fields.
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?
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.
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?
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
Retrieving data ...