0 Replies Latest reply: Aug 30, 2012 9:49 AM by wtheunissen RSS

    Using FirstSortedValue in Set Analysis

      I have a QVW of branch visits, where each branch is scored. Some branches are visited more than once (follow up). 

       

      I would like to report on these follow up visits, and the score achieved in each visit.

       

      The table should seperately list each branch by:

      Dimensions:

      SBU, Storename, Date

       

      Expressions:

      Points scored (PS) previously, Points available (PA) previously, Previous score (PS / PA %) as well as;

      Points scored (PS) latest, Points available (PA) latest, Latest score (PS / PA %)

       

      The Previous lookup works fine.  I am having trouble getting the Latest lookups right.

       

      I have tried aggr and set analysis using FirstSortedValue, but cannot seem to get it right - at best it displays both old and new scores, instead of only the latest scores.  The latest visit is also not neccessarily on the global Max date, hence I used FirstSortedValue to find it.

       

      The selection also needs to stay dynamic, so a script solution will not assist.

       

      The file is attached; any help will be much appreciated.