topic Re: Using a variable in set analysis in Qlik Sense in App Development

Using a variable in set analysis in Qlik Sense

chrishayes — Tue, 24 Jan 2017 19:34:25 GMT

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.

Re: Using a variable in set analysis in Qlik Sense

sunny_talwar — Tue, 24 Jan 2017 19:42:40 GMT

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])

Re: Using a variable in set analysis in Qlik Sense

girirajsinh — Tue, 24 Jan 2017 19:48:10 GMT

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])

Re: Using a variable in set analysis in Qlik Sense

chrishayes — Tue, 24 Jan 2017 20:03:25 GMT

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.

Re: Using a variable in set analysis in Qlik Sense

swuehl — Tue, 24 Jan 2017 20:04:07 GMT

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])