How the LastPeriods function works with the OVER Keyword (with and without Post-Aggregation)

How the LastPeriods function works with the OVER Keyword (with and without Post-Aggregation)

The LastPeriods function with the OVER keyword always considers all the underlying rows from the current node to n - 1 previous nodes with the specified aggregations. This should not be mixed with the concept of post-aggregation where you consider the already aggregated result set in LastPeriods function from the current node to n - 1 previous nodes.
 
For example, you want to calculate Average Sales for last four quarters so you use expression like:

Avg([Sales]) OVER (LastPeriods(4, [Axis.X]))

This will do a sum of Sales for four quarters divided by the count of rows for those four quarters included in that visualization (rows can be excluded by data limitation or limit by marking or filtering). In this example, the dataset consists of the following:

• 10 rows for Quarter 1 with total sales as $100
• 10 rows for Quarter 2 with total sales as $200
• 25 rows for Quarter 3 with total sales as $300
• 50 rows for Quarter 4 with total sales as $400

  Now you want to calculate Average Sales per quarter and then the average for last four quarters like post-aggregation which is done using the THEN keyword as seen below. Here the average is calculated for each quarter node first and then the average is calculated again for the 4 last quarters using the LastPeriods function.

 
 
Now say your requirement is to add incentive price of each quarter from averages sales of each quarter over last four quarters. In that case, you will not be able to use an expression like:

Avg([Sales]) THEN Avg([Value]) OVER (LastPeriods(4,[Axis.X])) + Sum([Price])

because the part of the expression after the THEN keyword is calculated on top of the aggregated data, and the aggregated value is referred to using the [Value] name. So the expression will not recognize any other columns like Price etc. though they are present in your data set. In order to achieve your requirement, you will need to replace post aggregation part (Avg([Sales]) THEN Avg([Value]) OVER (LastPeriods(4,[Axis.X]))) with a custom expression which will calculate Average Sales per quarter and then average for last four quarters and then add the sum of incentives price for each quarter.
Your custom expression will be like below:

(Sum(avg([Sales]) OVER (Intersect([Axis.X])) / Count() OVER (Intersect([Axis.X]))) OVER (LastPeriods(4,[Axis.X])) / UniqueCount([Quarter]) OVER (LastPeriods(4,[Axis.X])))+Sum([Price])

When you do Sum(Avg([Sales])) in any visualization, it will calculate Avg([Sales]) for each row and then Sum it up. In our dataset, Avg([Sales]) will be approx. 10.53 and then sum for each quarter.
So Quarter 1 has 10 rows so value will be 105.3 same will be for Quarter 2. For Quarter 3, it will be 10.53* 25 and for Quarter 4, it will be 10.53*50

 
As you got the Average Sales per quarter and then average for last four quarters now you can easily add it with the sum of Incentives Prices:

(Sum(avg([Sales]) OVER (Intersect([Axis.X])) / Count() OVER (Intersect([Axis.X]))) OVER (LastPeriods(4,[Axis.X])) / UniqueCount([Quarter]) OVER (LastPeriods(4,[Axis.X]))) + Sum([Price])

For a demonstration of this, see the attached LastPeriodsFuncDemo.dxp.
  Doc: Aggregations in Expressions

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_aggregations_in_expressions.htm

Doc: OVER Functions

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_over_functions.htm

Doc: Over in Custom Expressions

• https://docs.tibco.com/pub/sfire-analyst/7.12.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_over_in_custom_expressions.htm

Doc: Advanced Custom Expressions

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_advanced_custom_expressions.htm