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.
