New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for
Did you mean:  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  Creator III

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  Partner

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  Contributor II
Author

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."?  MVP & Luminary

Try it with:

vR52:

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

and

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

- Marcus  Creator III

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  Contributor II
Author

Hi Carlos,

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

Hi,

Tried, but got "-" in the table.  Contributor II
Author

... and if i switch the order to:

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

i get the category but the formula ignore vR52.  MVP & Luminary

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  Creator III

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) Tags
Community Browser