How the LastPeriods function works with the OVER Keyword (with and without Post-Aggregation)
Article ID: KB0081649
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All Versions |
Description
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: 
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:
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 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.
Issue/Introduction
This article explains how the LastPeriods function works with the OVER Keyword (with and without Post-Aggregation)
Resolution
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:
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
But you want to calculated Avg of Sales per quarter so instead of Avg([Sales]) you have to do Avg([Sales]) Over(Intersect([Axis.X])) and you will get below result:
This is the Sum of Sales for each quarter because Avg Sales for each quarter is summed up for all rows in that particular quarter. But you want to get average not the sum of sales so you can divide this value by the number of rows and each row value will be summed up like for quarter 1 avg of Sales is 10 and number of rows is also 10 so 10/10 + 10/10 + 10/10 … result will be 10
Now you got the average of Sales for each quarter. Sum these values for last 4 quarters using LastPeriods function.
But you want Average Sales per quarter and then average for last four quarters so you will divide this sum by count of quarters to get average
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:
For a demonstration of this, see the attached LastPeriodsFuncDemo.dxp.
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
But you want to calculated Avg of Sales per quarter so instead of Avg([Sales]) you have to do Avg([Sales]) Over(Intersect([Axis.X])) and you will get below result:
This is the Sum of Sales for each quarter because Avg Sales for each quarter is summed up for all rows in that particular quarter. But you want to get average not the sum of sales so you can divide this value by the number of rows and each row value will be summed up like for quarter 1 avg of Sales is 10 and number of rows is also 10 so 10/10 + 10/10 + 10/10 … result will be 10
Now you got the average of Sales for each quarter. Sum these values for last 4 quarters using LastPeriods function.
But you want Average Sales per quarter and then average for last four quarters so you will divide this sum by count of quarters to get average
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.
Additional Information
Doc: Aggregations in Expressions
Doc: OVER Functions
Doc: Over in Custom Expressions
Doc: Advanced Custom Expressions
Attachments
Feedback
Yes
No
Powered by
