Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Using a variable in set analysis in Qlik Sense

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.

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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.

View solution in original post

4 Replies
Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted
Contributor III
Contributor III

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.

View solution in original post

Highlighted
MVP
MVP

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