Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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;

2 Replies

Re: Computing %-change and delta in load statement

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

jleefjcapital
Contributor

Re: Computing %-change and delta in load statement

Thank you!

Community Browser