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