Home > OLAP Calculation Templates > Period to Date > Difference from Period To D...
![]() Previous |
![]() Next |
Calculates the difference in values aggregated to the current time period compared to the same calculation up to an earlier period.
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.
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.
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.
LAG_VARIANCE(SUM(UNITS_CUBE.UNITS) OVER HIERARCHY (TIME.FISCAL BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN GREGORIAN YEAR),1) OVER HIERARCHY (TIME.FISCAL)
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