I'm trying to compute %-change and delta variables derived from a loaded time series variable in the load statement. The following statements works great if I'm only interested in the aggregate change over time, but it breaks down when I add another dimension to it.
For instance, the time series is an aggregate of multiple regions in the US. Hence, if I add a dimension and select a subset of the regions, the %-change and delta values continue to reflect the aggregate changes.
Here's the code that I used-
Join(EARNINGS_MSA)
LOAD *,
[TOTAL_TOTPRIV_MSA] - Alt(Peek('TOTAL_TOTPRIV_MSA'), [TOTAL_TOTPRIV_MSA]) as TOTPRIV_MSA_Delta,
([TOTAL_TOTPRIV_MSA] - Alt(Peek('TOTAL_TOTPRIV_MSA'), [TOTAL_TOTPRIV_MSA]))/[TOTAL_TOTPRIV_MSA] as [TOTPRIV_MSA_%],
[TOTAL_RETAIL_MSA] - Alt(Peek('TOTAL_RETAIL_MSA'), [TOTAL_RETAIL_MSA]) as RETAIL_MSA_Delta,
([TOTAL_RETAIL_MSA] - Alt(Peek('TOTAL_RETAIL_MSA'), [TOTAL_RETAIL_MSA]))/[TOTAL_RETAIL_MSA] as [RETAIL_MSA_%];
LOAD DATE,
Sum(TOTPRIV_MSA) as TOTAL_TOTPRIV_MSA,
Sum(RETAIL_MSA) as TOTAL_RETAIL_MSA
Resident EARNINGS_MSA
Group By DATE
Order By DATE;
Join(EMPLOYEES)
LOAD *,
[TOTAL_RETAILTRADE_MSA] - Alt(Peek('TOTAL_RETAILTRADE_MSA'), [TOTAL_RETAILTRADE_MSA]) as RETAILTRADE_MSA_Delta,
([TOTAL_RETAILTRADE_MSA] - Alt(Peek('TOTAL_RETAILTRADE_MSA'), [TOTAL_RETAILTRADE_MSA]))/[TOTAL_RETAILTRADE_MSA] as [RETAILTRADE_MSA_%],
[TOTAL_CONSTR_MSA] - Alt(Peek('TOTAL_CONSTR_MSA'), [TOTAL_CONSTR_MSA]) as CONSTR_MSA_Delta,
([TOTAL_CONSTR_MSA] - Alt(Peek('TOTAL_CONSTR_MSA'), [TOTAL_CONSTR_MSA]))/[TOTAL_CONSTR_MSA] as [CONSTR_MSA_%];
