Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to combine Variable and fields in an expression?

I like to do this:

=sum({<fin_PORTFOLIO=, $(vMTD_PART( $(vAP), $(vFYEAR) ))>}($(vMeasure)))

Variable is:

SET vMTD_PART = AP={$1},F_YEAR={$2},fin_SalesGroup={'SKU'}

How can I do this?



10 Replies
pover
Luminary Alumni
Luminary Alumni

1. In the GUI create variables vAP and vFYEAR in an inputbox to make then dynamic.

2. Then create a variable vMTD_PART as being

='AP={$(vAP)},F_YEAR={$(vFYEAR)},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'}'

(Note the use of chr(39) for single quotes)

3. In your expression put

=sum({<fin_PORTFOLIO=, $(vMTD_PART)>} ($(vMeasure)))

I recommend testing each step in a text object.

Regards.

Not applicable
Author

Great, thanks, it works for "=" Assign, but it does not work for less than or greater than, i.e:

does not work:

='AP={"<=$(=$1)"},F_YEAR={$2},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'}'

What is wrong hear? this is a YTD function which should be range from month 1 to $1

The variable nightmare continues....

regards



Not applicable
Author

I tried this, this doen't work either:

='AP={'&chr(34)&'<=$(=$1)'&chr(34)&'},F_YEAR={$2},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'}>}'



Miguel_Angel_Baeyens

Hello,

Is $1 the name of a variable or a field?, In any case, "less than or equal to" works as follows

Sum({< FieldName = {'>=$(=Date($(vVariableFrom))<=$(=Date$(vVariableTo))'} >} Amount)


In bold, calling the variable "vVariableFrom". Dolar expansion may be or may be not needed.

Underscored calling a function $(=Function(whatever)). The syntax is always like this.

I've single quoted the value since I want to use a literal. However, quoting is not needed for numbers (or for a variable which will return a number)

Sum({< Year = {$(vCurrentYear)} >} Amount)


If you are going to use searchable values, then use the double quote

Sum({< Year = {"20*"} >} Amount)


Hope that helps

Not applicable
Author

ok, so far so good, still does not work.

What about your example:

Sum({< Year = {$(vCurrentYear)} >} Amount)

can you change this formual to LESS THAN?

Sum({< Year = {'<=$(vCurrentYear)} >} Amount) ??

Still the problem with Less Than

Not applicable
Author

This seems to work:

sum({<AP={'<=$(=$(vAP))'},F_YEAR={$(vF_YEAR)},fin_SalesGroup={'SKU'} >} $(vMeasure))

Now i try to quote it, because i want have parts of formula in order to dynamic put together:

='AP={'&chr(39)&'<=$(=$(vAP))'&chr(39)&'},F_YEAR={$(vF_YEAR)},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'} >}'



Usage:

=sum({< $(vYTD_SKU)>} ($(vMeasure)))

and does not work, what a surprise



Not applicable
Author

These don't work either:

='AP={"<=(=GetFieldSelections(AP))"},F_YEAR={$2},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'}'

='AP={' & chr(34) & '<=(=GetFieldSelections(AP))' & chr(34) &'},F_YEAR={$2},fin_SalesGroup={'&chr(39)&'SKU'&chr (39)&'}'

='AP={' & chr(39) & '<=(=GetFieldSelections(AP))' & chr(39) &'},F_YEAR={$2},fin_SalesGroup={'&chr(39)&'SKU'&chr(39)&'}'



The QlikView Variable and Expression nightmare continuew, .NET and VB-Expression are 1000 times better to understand!!



Miguel_Angel_Baeyens

That's correct except for a little typo (the closing quote):

SUM({< YEAR = {'<=$(VCURRENTYEAR)'} >} AMOUNT)


I assume that YEAR and VCURRENTYEAR will have an equivalent value (2010-2010, not 2010-10, for example).

Regards.

Miguel_Angel_Baeyens

Hi,

I've attached an example (not Personal Edition, if you cannot see it let me know) with variables within variables and the element funciton P() to consider all selected values within a field. I'm SETting variables in the load scipt, don't knwo if that suits you but I'll give it a try.

Hope this helps.