Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
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,