How to combine Variable and fields in an expression?

[#1]

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?

[pover]

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.

[#3]

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

[#4]

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

[#6]

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

[#7]

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

[#8]

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.