Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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.
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."?
Try it with:
vR52:
Year=,YearMonth=, Quarter=, Month=, Week=, Date =, DateNum = {">=$(vSelectedDate364)<=$(vSelectedDate)"}
and
Sum({<Category = {'X'},$(vR52)>} Sales)
- Marcus
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
Hi Carlos,
I get i value now. But it seams to ignore the category conditions.
Hi,
Tried, but got "-" in the table.
... and if i switch the order to:
Sum($(vR52){<Category = {X'}>} Sales)
i get the category but the formula ignore vR52.
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
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)