4 Replies Latest reply: Mar 1, 2016 3:34 PM by Ben Roberts RSS

    Peek Help - Groups and First Row Values

    Ben Roberts

      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!