Please post your GetNthRecord calculation
"RTM_CustomersAtStart = is a calculation field that grabs the value in RTM_Customers_At_Start from the next record in the found set using GetNthRecord."
Does not make sense. Mistyped perhaps?
Yes. You’re right. It was a typo.
RTM_CustomersAtStart = GetNthRecord ( c_Customers_At_End;;Get(RecordNumber )+1)
Sorry about that and thanks for any help.
I'm puzzled as to why this copies the data from the next record rather than the previous record. Seems to me that you should subtract 1 instead of adding 1 so that the start value copies the end value of the previous month.
But as to the calculation itself, it will return a ? Indicating an evaluation error for the last record as there is no next record to access with the getNthRecord function. So that would explain the missing last data point on the chart.
You can put your GetNthRecord inside an If function that checks to see if the Nth record will be out of bounds before allowing it to evaluate to eliminate getting an error, but you still have to decide what value to return when there is no Nth record.
The sort order is making the difference in the calc using GetNthRecord. Neither +1 nor -1 works in both Table and Chart views regardless of how I sort the End Date.
If I find a subset of the records and sort in descending order based on the end date, all the values are shown correctly in table view - including the Churn Rate for the most recent period. But when I switch to the Chart, I can’t put the most recent date on the right AND show a Churn Rate. If I change the sort order, putting the most recent date on the left, the churn rate is correct but the Churn rate for the oldest date is blank. Changing the GetNthRecord to -1 just reverses the problem. In either case, one of the values is blank.
I’d like to show the Churn Rate for all dates in the found set - of course.
I thought GetNthRecord would be a great solution. e.g. The starting count for the current period must be the ending count for the previous period.
But I may need to add several more fields to calculate the Starting Customer count for each period. I think that would solve the problem - I just didn’t want to add more fields than necessary.
As always, thanks for your help. You’re a great help to everyone on this forum.
You are correct that you will either be missing the first or last value. That is inherent to the type of calculation you are doing. The unanswered question: what value should be returned in place of that error?
Note what I wrote about using an IF function. You can use that function to avoid the error, but what value should your calculation use when there is no next or previous record to get?