Calculation field based on Sub-summary Data
I want a field to calculate the number of Net New Clients added each year by subtracting the number of cancellations from the number of New Clients.
My table includes date fields showing the date a customer becomes a customer and another field showing the data of cancellation if any. Two more calculation fields get the Year of the Order and the Year of Cancellation - if any.
Two summary fields count the number of New Clients and Cancellations by counting the values in the Date Ordered and Date Cancelled fields.
The two Summary fields are: s_TotalOrders and. s_TotalCancellations.
For each year, NetNewClients should be the number of New Clients added minus the number of Client Cancellations.
So, for each year starting 2005, I'd like to see the number of New Clients added, the number of Cancellations and the Net. Screenshot showing sample data is attached.
The GetSummary function is not working in this case because (I think) the break field cannot be the same. The year ordered is not necessarily the year cancelled.
I'm sure I'm missing something simple. How should I be calculating the Net New Clients?