Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 )
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 )
Absolutely - seems to work!!
Much cleaner than using Peek too - many thanks
Used above in the expression to get the same results.
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)