Percentage difference from previous value analytic function
This article describes how to use the Percent Difference from Previous Value analytic function in CDP Data Visualization.
Click the field on the measurement shelf.In this example, the
sum(population)field on the Y Axis shelf.
In the FIELD PROPERTIES menu, expand Analytic
Functions, and select % Difference from
In the Analytic Function: Percentage Difference from Previous
Value modal window:
In Select aggregate for this measure, leave the default Sum. The other aggregation options are Count, Minimum, Maximum, and Average. In addition, some connection types also support Approximate Distinct Count and Exact Distinct Count.
Under Select entities over which to take % difference from previous value, select country. This is the grouping that applies when calculating the % difference from previous values.
Under Select the sort order, you can specify the order of sorting. This order specifies what the previous row is, and the row where the value is taken. If your visual already specifies sorting order, these values appear in the modal.
In this example, edit the sort order to be: country ascending, followed by year ascending.
- Under Offset, specify the number of previous rows over which the function executes.
- Click APPLY.
If you examine the expression in the Enter/Edit Expression
interface, it shows that the function has the following form:
(sum([population]) - LAG(sum([population]), 1) over (partition by [country] order by [country], [year])) / LAG(sum([population]), 1) over (partition by [country] order by [country], [year]) as 'pct diff from prev(population)'
Click REFRESH VISUAL.
You can see the change in the appearance of the Y axis and the tooltip, from displaying the
sum(population)in millions to
populationas a percentage difference from previous value.
Hover over any country to view the percentage difference of population from the previous year. In the following example, the tooltip shows an increase in the population of Libya from previous year 1964 to the current year 1965 by 3.92%.