5 Replies Latest reply on Feb 12, 2017 11:03 AM by philmodjunk

    Charting Problem related to GetNthRecord


      I'm having a problem charting (column)  the last value in a data series. I think it has to do with another dependent field, also a calculation) that uses GetNthRecord to grab a value from the next record in the found set.


      Only one table is involved. RTM_Periods. Each record hold values for a 12 month period.


      RTM_Period_Start is the starting date for the 12 month period.

      RTM_Period_End is the end date of the 12 month period.

      RTM_CHURN is the number of Orgs that canceled their service in the 12 month period.

      RTM Customers_At_End is the number of customers at the end of the period.

      RTM_CustomersAtStart = is a calculation field that grabs the value in RTM_Customers_At_End from the next record in the found set using GetNthRecord. (edited to correct error in original post)

      RTM_CHURN_Rate = RTM_CHURN / RTM_CustomersATStart


      I want to chart the RTM_CHURN_Rate.

      • x axis = RTM_Period_End
      • y axis = RTM_CHURN_Rate.


      When viewed in table or list view, the values needed for this chart are displayed correctly but when using the same found set, the last datapoint in the series (RTM_CHURN_Rate) is blank. The screenshot below is actually 2 screenshots combined into one. The table view shows the correct values for the periods ending Nov, Dec and Jan 2017. The table view also shows that the clients at Start is the same value as the Clients at End for the next record. The column chart shows the problem. The values for 1/31/2017 aren't showing. Should be 14.11%.


      Any help would be appreciated.


        • 1. Re: Charting Problem related to GetNthRecord

          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?

          • 2. Re: Charting Problem related to GetNthRecord

            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.

            • 3. Re: Charting Problem related to GetNthRecord

              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.

              • 4. Re: Charting Problem related to GetNthRecord

                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.

                • 5. Re: Charting Problem related to GetNthRecord

                  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?