Set Expression Not Picking Relevant Number

[Anonymous]

I attached a QVW that shows a chart by Region with Dates (maximum date from previous year based on selected date) and Calculation in the next column. For Jul 12, 2013, it shows correct maximum date for last year by each region but does not show any value for Japan (works fine for other regions though), even though that region has only one number for Dec 28, 2012.

Would appreciate any suggestion.

[swuehl · Accepted solution]

There is no record for 31Dec12 for Japan, that's why the last line doesn't show anything.

You'll need to find the latest (per last year's date) record per region (dimension), so set analysis alone won't be enough.

Try FirstSortedvalue() in combination with a set expression:

=(LIST_REL_PERF+1) / (FirstSortedValue({<CLP_Calendar.CLP_Date={"<$(=makedate(2013))"}>} LIST_REL_PERF, -CLP_Calendar.CLP_Date)+1)-1

Check also attached.

[Anonymous]

Thank you!