Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
simoncarts
Contributor II
Contributor II

Looking Up Value based on Week Number

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?

Labels (1)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

 

View solution in original post

1 Reply
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,