Skip Headers
Previous
Previous
 
Next
Next

Difference from Period To Date Period Ago

Calculates the difference in values aggregated to the current time period compared to the same calculation up to an earlier period.

Template

Difference between the current { Gregorian level | ancestor at level } to date and { Gregorian level | ancestor at level level } number period ago for measure (...) in the time dimension and time hierarchy hierarchy. Aggregate using { SUM | MAXIMUM | MINIMUM | AVERAGE } from the { beginning | end } of the period.

Example

This calculated measure superimposes the Gregorian year on the Fiscal hierarchy to define the range of time periods included in the calculation. It computes the difference between the running totals for the current period and for the previous period within a Gregorian year. The calculation begins with the last period in the Gregorian year and ends with the current period.

Calculation

Difference between the current Gregorian Year to date and Gregorian year to date 1 period ago for UNITS (...) in the TIME dimension and TIME.FISCAL hierarchy. Aggregate using SUM from the end of the period.

OLAP Expression Syntax

LAG_VARIANCE(SUM(UNITS_CUBE.UNITS) OVER HIERARCHY (TIME.FISCAL BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN GREGORIAN YEAR),1) OVER HIERARCHY (TIME.FISCAL)

Query Results

The Diff column shows the difference in Units sold to date between the current period and the previous period. The calculations are performed over Gregorian years, so that Q3 FY-04 to Q2-FY05 are calculated together in Gregorian year 2004, and Q3-05 to Q2-06 in 2005. In this example, the values are totaled from the end of the Gregorian year. The fiscal years are not contained in the Gregorian years, so they are not included in the calculations.

Time      Ending       Units To Date Period Ago     Diff
--------- ---------- ------- ------- ---------- --------
Q1 FY-04  30-SEP-03   140931  286647     423965  -137318
Q2 FY-04  31-DEC-03   145716  145716     286647  -140931
Q3 FY-04  31-MAR-04   146529  587419     145716   441703
Q4 FY-04  30-JUN-04   143070  440890     587419  -146529
Q1 FY-05  30-SEP-04   148292  297820     440890  -143070
Q2 FY-05  31-DEC-04   149528  149528     297820  -148292
Q3 FY-05  31-MAR-05   143607  565718     149528   416190
Q4 FY-05  30-JUN-05   138096  422111     565718  -143607
Q1 FY-06  30-SEP-05   138953  284015     422111  -138096
Q2 FY-06  31-DEC-05   145062  145062     284015  -138953
Q3 FY-06  31-MAR-06   146819  584929     145062   439867
Q4 FY-06  30-JUN-06   145233  438110     584929  -146819
FY2004    30-JUN-04   576246    null       null     null
FY2005    30-JUN-05   579523    null       null     null
FY2006    30-JUN-06   576067    null       null     null