Set analysis with interactive variable

[p_1]

i am using set analysis with a variable already created in the script, but i want it to be interactive with the filters done in the dashboard.

my variables are as follow, where 'year' is a field in my data:

let vYearMax = Num(Max('year'));

let vYearMaxM1 = Num(Max('year')-1);

let vYearMaxM2 = Num(Max('year')-2);

let vYearMaxM3 = Num(Max('year')-3);

set analysis expression:

sum ( { < year = { "$(=$(vMaxYear))" } > } Amount)

it is only working for the higher year available in my data that is: 2020

when filtering on year 2018 for example the value is not changing, and i want it to show me the amount in 2018

[GaryGiles]

Why are there quotes around the year field?

In the load script, you would want the quotes around the entire variable string.

let vYearMax = 'Num(Max(year))';

[p_1]

Thank you @GaryGiles ,but the set analysis is only showing the values related to the max year which is 2020 and it is not changing the values when i select 2018 from the filter

[Vegar]

Check your variables in the application . Are they showing the expression or static year values? When using LET the value is calculated when the variable is set running the script. Try changing to SET. It also looks like your expression is wrong syntax. Don't use the string 'year', you should use the field [year]. See my adjustments below

SET vYearMax = Num(Max([year] ));

SET YearMaxM1 = Num(Max([year] )-1);

SET vYearMaxM2 = Num(Max([year] )-2);

SET vYearMaxM3 = Num(Max([year] )-3);

[Ksrinivasan]

hi,

use 

my data

when no selection in year filter:

when select 2021

when select 2020

Ksrinivasan.

GetCurrentSelections ( chr(13)&chr(10) , ' = ' )

[p_1]

@Vegar it is showing the number of year, that is why it is static and not changing with the filter?

[p_1]

i am trying to apply below expression but it is not working

sum({<year={"$(=GetFieldSelection(year))"}>}Amount)

[Ksrinivasan]

hi,

not that. use below one remove first and last '

ksrinivasan

[Vegar]

Take a look at the attached qvf file.

Expressions: 

sum ( { <Year={'$(=$(vYearMax))' }       > } Amount)

sum ( { <Year={'$(=$(vYearMaxM1))' } > } Amount)

sum ( { <Year={'$(=$(vYearMaxM2))' } > } Amount)

sum ( { <Year={'$(=$(vYearMaxM3))' } > } Amount)

SET vYearMax = Num(Max([Year] ));
SET vYearMaxM1 = Num(Max([Year] )-1);
SET vYearMaxM2 = Num(Max([Year] )-2);
SET vYearMaxM3 = Num(Max([Year] )-3);

/**DATA**/
LOAD * inline [
Year, Region, Amount
2018, north,  10
2018, south,  12
2019, north,  11
2019, south,  10
2020, south,  9
2020, north,  19
2021, north,  22
2021, south,  10
];