Computing %-change and delta in load statement

jleefjcapital — Tue, 14 Jul 2015 17:57:22 GMT

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;

Re: Computing %-change and delta in load statement

sunny_talwar — Tue, 14 Jul 2015 18:03:15 GMT

To make it dynamic, we will have to work on a front end solution. This solution is not going to work Jessica.

I have your document, I will see if I can make it to work.

Best,

Sunny

Re: Computing %-change and delta in load statement

jleefjcapital — Tue, 14 Jul 2015 18:06:39 GMT

Thank you!

topic Re: Computing %-change and delta in load statement in QlikView

Computing %-change and delta in load statement

Re: Computing %-change and delta in load statement

Re: Computing %-change and delta in load statement