Looking Up Value based on Week Number

[simoncarts]

I'm trying to return a value based on the Highest Week Number in our Data.

The below works with the Highest Week Number hardcoded into the formula as shown below.
=Max({<[TargetTable.Week Number] ={42} >} [TargetTable.Target %])

However, when I replace the hardcoded Week Number with the formula to return the highest week number 'Max([Orders.Week Number])' as shown below,  I get an error reading Error in Set Modifier ad hoc element list: ';' or ')' expected

=Max({<[TargetTable.Week Number] =Max([Orders.Week Number])>} [TargetTable.Target %])

Below are snippets of the 2 relevant tables 

TargetTable
Week Number	Target %
40	80%
41	81%
42	82%
43	83%
44	84%
45	85%
46	86%

Orders
OrderID	Week Number
1567	40
1568	40
1569	40
1570	41
1571	40
1572	40
1573	41
1574	40
1575	41

What am I doing wrong?

[Oleg_Troyansky · Accepted solution]

Hi Simon,

You are almost there, but you need to use a very particular syntax in your Set Analysis condition. You can't simply write "field = formula", instead you need to always enclose the "selected" values in curly brackets {...} and if you need to calculate something, you should enclose your calculation in a $-sign expansion. Your syntax should look like this:

=Max({<[TargetTable.Week Number] = {$(=Max([Orders.Week Number]))}>} [TargetTable.Target %])

This is in case you want the max([Orders.Week Number]) to respect user selections. If you want an absolute maximum, no matter what is selected, then add another Set Analysis {1} in that aggregation:

=Max({<[TargetTable.Week Number] = {$(=Max({1} [Orders.Week Number]))}>} [TargetTable.Target %])

I'd even recommend to define a variable and calculate it outside of your set analysis, to improve performance of this formula:

set vMaxWeek = '=Max({1} [Orders.Week Number])'; // Notice that the formula should begin with the equals sign

Then you can use the variable in your Set Analysis condition, and it will work better:

=Max({<[TargetTable.Week Number] = {$(vMaxWeek)}>} [TargetTable.Target %])

Join us at the Masters Summit for Qlik in New Orleans on November 14-16 to learn these and many other advanced development techniques!

Cheers,