Using FirstSortedValue in Set Analysis

Thu, 30 Aug 2012 13:49:46 GMT

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.

topic Using FirstSortedValue in Set Analysis in QlikView

Using FirstSortedValue in Set Analysis