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.
ben2roberts
Contributor

Peek Help - Groups and First Row Values

Hi all, I am having some problems with Peek. I have researched across the boards but cannot find the final pieces to solve this.

Essentially I am trying to calculate a monthly change % value from the field 'UV_AMT' (see excel / Qlik file attached).

UV_AMT represents the month by month value of an account (denoted by first column 'PERF_GRP_KEY').

In this dummy file I have duplicated the first account and renamed EG00002 so I can test what would happen with multiple entries (my main DB has 1,000s of these 'EGs'.

I was able to create the PCT_RTN_BR calculation using the formula:

     (UV_AMT - Peek(UV_AMT,-1)) / Peek(UV_AMT,-1)

I am trying to match this with the column PCT_RTN_EXCEL_CALC which is the value calculated in excel manually.

The above formula works for all but the following 2 cases:

1 - very first row of the table, I get a value of null instead of '0'

2 - where the data switches to EG0002 the Peek is picking up the last row on the previous EG.

I tried to solve using GROUP clause on PERF_GRP_KEY but could not make it work.

Any help much appreciated!

Thanks,

Ben

1 Solution

Accepted Solutions
Not applicable

Re: Peek Help - Groups and First Row Values

As per my understanding , if account switches to new values you need to start from 0.

Try like below:

IF( AcctName = Previous(AcctName) , (UV_AMT - Previous(UV_AMT))/Previous(UV_AMT) , 0 )

4 Replies
Not applicable

Re: Peek Help - Groups and First Row Values

As per my understanding , if account switches to new values you need to start from 0.

Try like below:

IF( AcctName = Previous(AcctName) , (UV_AMT - Previous(UV_AMT))/Previous(UV_AMT) , 0 )

ben2roberts
Contributor

Re: Peek Help - Groups and First Row Values

Absolutely - seems to work!!

Much cleaner than using Peek too - many thanks

Not applicable

Re: Peek Help - Groups and First Row Values

Used above in the expression to get the same results.

ben2roberts
Contributor

Re: Peek Help - Groups and First Row Values

Hi Dathu - I wonder if you may be able to advise me further - I implemented the code in my master DB but it did not work. In most of the case the % calculated correctly, but not always.

I added a new column to my output looking at just Previous(UV_AMT) and the results look like they are coming through in the wrong order.

Is there something I can add to make sure it is all considered in sequence of month end date (assuming that is what is happening)

Community Browser