Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jleefjcapital
Contributor

Computing %-change and delta in load statement

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;

Community Browser