Peek Help - Groups and First Row Values

[Anonymous]

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

[#2 · Accepted solution]

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 )

[Anonymous]

Absolutely - seems to work!!

Much cleaner than using Peek too - many thanks

[#4]

Used above in the expression to get the same results.

[Anonymous]

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)