Percentage Difference from Next Value Analytic Function

This article describes how to use the Percent Difference from Next Value analytic function in Arcadia Data.

We are using the Basic Visual for Aggregates to demonstrate how to use the Percentage Difference from Next Value analytic function.

  1. Click the field on the measurement shelf, in this case, the sum(population) field on the Y Axis shelf.
  2. In the Field Properties menu, expand Analytic Functions, and select % Difference from Next.
    Applying % Difference from Next Value
  3. In the Analytic Function: Percentage Difference from Next Value modal window,

    1. In Select aggregate for this measure, leave the default Sum. The other aggregation options are Count, Approximate Distinct Count, Exact Distinct Count, Minimum, Maximum, and Average.
    2. Under Select entities over which to take % difference from next value, select year. This is the grouping that applies when calculating the % difference from next values.
    3. Under Select the sort order, you can specify the order of sorting. This order specifies what the next row is, and the row where the value is taken. If your visual already specifies sorting order, these values appear in the modal.

      You can add additional sort parameters, by clicking the (add/plus) icon.

      Here, leave the default order: year ascending, followed by country ascending.

    4. Under Offset, specify the number of next rows over which the function executes.
    defining the Percentage Difference from Next Value analytic Function
    Analytic Function: Percentage Difference from Next Value
  4. Click Apply.
  5. [Optional] If you examine the expression in the Enter/Edit Expression interface, it shows that the function has the following form:

    (100 * (sum([population]) - LEAD(sum([population]), 1) 
      over (partition by [year] order by [year], [country])) / LEAD(sum([population]), 1) 
      over (partition by [year] order by [year], [country])) 
      as '% difference from next value(population)'
  6. Click Refresh Visual.

  7. Notice the change to the appearance of the visual, and the change in values that appear on the tooltip.

    Percentage Difference from Next Value Analytic Function Visual
    Percentage Difference from Next Value Analytic Function Visual