Using a variable in set analysis in Qlik Sense

[chrishayes]

I am trying to use a variable in a set analysis expression. The variable is being set in my script:

          LET v_prior_year = Year(MonthStart(Today(1)) - 1) - 1;

And the set analysis expression I am trying to get to work:

          Sum({$<[Invoice Year]={$(=v_previous_year)}>}[Ship Amount]) / Sum({$<[Invoice Year]={$(=v_previous_year)}>}[Ship Quantity])

I've tried different variations based on what I've read here, but none of them seem to work. I am using Qlik Sense 3.1. Any help is appreciated.

[sunny_talwar]

Can you check the value of v_prior_year in a KPI object? Does it gvie you 2016? May be you need another dollar sign expansion

Sum({$<[Invoice Year]={$(=$(v_previous_year))}>}[Ship Amount]) / Sum({$<[Invoice Year]={$(=$(v_previous_year))}>}[Ship Quantity])

[girirajsinh]

Hi Christopher,

Does it work without use of variable ? Have you tired it first?

I mean if you put expression Year(MonthStart(Today(1)) - 1) - 1 directly into set analysis does it give you desired result?

If so then you may try using variable following way,

LET v_prior_year = 'Year(MonthStart(Today(1)) - 1) - 1';

$(=$(=v_previous_year))

   Sum({$<[Invoice Year]={$(=$(=v_previous_year))}>}[Ship Amount]) / Sum({$<[Invoice Year]={$(=$(=v_previous_year))}>}[Ship Quantity])

[chrishayes · Accepted solution]

Sorry folks, it looks like I was using the wrong variable v_previous_year instead of v_prior_year. The former was null which I realized when checking the value in a KPI object. Thanks.

[swuehl]

Your variable should already hold the year value, so you won't need to expand using the equal sign:

  Sum({$<[Invoice Year]={$(v_previous_year)}>}[Ship Amount]) / Sum({$<[Invoice Year]={$(v_previous_year)}>}[Ship Quantity])