0 Replies Latest reply: Jul 14, 2015 1:58 PM by Jes Lee RSS

    Computing %-change and delta in load statement

    Jes Lee

      Hello,

       

      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_%];

      LOAD DATE,

           Sum(RETAILTRADE_MSA) as TOTAL_RETAILTRADE_MSA,

           Sum(CONSTR_MSA) as TOTAL_CONSTR_MSA

      Resident EMPLOYEES

      Group By DATE

      Order By DATE;