Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sebastian_fager
New Contributor II

Combine Variable with condition

Hi,

Want to see the rolling 52 weeks for sales on category X.

Have the following variable and analysis. But how do i combine them?

Set analysis:

Sum({<Category = {'X'}>} Sales)

Script:

LET vR52 = '{<Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = {">=$'&'(vSelectedDate364)<=$'&'(vSelectedDate)"}>}';

Variables:

vSelectedDate364:

=num($(vSelectedDate)-363)

vSelectedDate:

=Max(DateNum)

The variables and set analysis works, but can't figure out how to combine them..

Have tried:

Sum({<Category = {'X'},$(vR52)>} Sales)

1 Solution

Accepted Solutions
CarlosAMonroy
Contributor III

Re: Combine Variable with condition

What if you remove the brackets from the variable, like this:

LET vR52 = 'Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = {">=$'&'(vSelectedDate364)<=$'&'(vSelectedDate)"}';


and then the expression should be:


Sum({<Category = {'X'}, $(vR52)>} Sales)

10 Replies
MVP
MVP

Re: Combine Variable with condition

You can't easily combine them with the curly brackets in the variable. If you remove them (and leave the angle brackets in place, then this should work:

Sum({<Category = {'X'}>*$(vR52)} Sales)

Or remove the curly brace and angle brackets:

Sum({<Category = {'X'}>*<$(vR52)>} Sales)


or, the equivalent (I think):

Sum({<Category = {'X'}, $(vR52)>} Sales)


Make sure that vSelectedDate364 and vSelectedDate are defined with a leading zero equals sign.



Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sebastian_fager
New Contributor II

Re: Combine Variable with condition

Thanks for reply!

Change the variable to:

LET vR52Test = '<Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = ">=$'&'(vSelectedDate364)<=$'&'(vSelectedDate)">';

And tried

Sum({<Category = {'X'}>*$(vR52Test)} Sales)

I got "0" in the table.

What do you mean with "...are defined with a leading zero."?

Re: Combine Variable with condition

Try it with:

vR52:

Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = {">=$(vSelectedDate364)<=$(vSelectedDate)"}

and

Sum({<Category = {'X'},$(vR52)>} Sales)

- Marcus

CarlosAMonroy
Contributor III

Re: Combine Variable with condition

Hi Sebastian,

Leaving the variable the way you have it, You can try the following:

Sum({<Category = {'X'}>}$(vR52) Sales)



Hope that helps,

Carlos M

sebastian_fager
New Contributor II

Re: Combine Variable with condition

Hi Carlos,

I get i value now. But it seams to ignore the category conditions.

sebastian_fager
New Contributor II

Re: Combine Variable with condition

Hi,

Tried, but got "-" in the table.

sebastian_fager
New Contributor II

Re: Combine Variable with condition

... and if i switch the order to:

Sum($(vR52){<Category = {X'}>} Sales)

i get the category but the formula ignore vR52.

Re: Combine Variable with condition

Use these expression within a table without a lable for it - if you than hoover over the label Qlik will display how it interpreted the variables. I assume that vR52 and/or the included variables won't be interpreted correctly.

Beside them where and how did you define these variables. My suggestion based on a creating within the variable-editor because expression-variables with $-signs are difficult by creating within the script (and it made mostly not much sense to create them there - if you don't want to create the variables within the variables editor it's better to keep them complete external by using include-variables or using excel-tables for it).

- Marcus

CarlosAMonroy
Contributor III

Re: Combine Variable with condition

What if you remove the brackets from the variable, like this:

LET vR52 = 'Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = {">=$'&'(vSelectedDate364)<=$'&'(vSelectedDate)"}';


and then the expression should be:


Sum({<Category = {'X'}, $(vR52)>} Sales)

Community Browser