Qlik Community

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 
Search instead for 
Did you mean: 
sebastian_fager
Contributor II
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
Creator III
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)

View solution in original post

10 Replies
jonathandienst
Partner
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
sebastian_fager
Contributor II
Contributor II
Author

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."?

marcus_sommer
MVP & Luminary
MVP & Luminary

Try it with:

vR52:

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

and

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

- Marcus

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

sebastian_fager
Contributor II
Contributor II
Author

Hi Carlos,

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

sebastian_fager
Contributor II
Contributor II
Author

Hi,

Tried, but got "-" in the table.

sebastian_fager
Contributor II
Contributor II
Author

... and if i switch the order to:

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

i get the category but the formula ignore vR52.

marcus_sommer
MVP & Luminary
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

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

View solution in original post