10 Replies Latest reply: May 5, 2017 3:35 AM by Sebastian Fagerlund

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

• ###### 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.

• ###### Re: Combine Variable with condition

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

• ###### Re: Combine Variable with condition

Hi,

Tried, but got "-" in the table.

• ###### 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

• ###### 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

• ###### Re: Combine Variable with condition

Hi Carlos,

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

• ###### 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

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)

• ###### Re: Combine Variable with condition

Hi, this works great! Many thanks for all the responses!