topic How to combine Variable and fields in an expression? in QlikView

How to combine Variable and fields in an expression?

Wed, 01 Dec 2010 10:56:11 GMT

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?

How to combine Variable and fields in an expression?

pover — Wed, 01 Dec 2010 23:21:22 GMT

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.

How to combine Variable and fields in an expression?

Fri, 03 Dec 2010 15:35:40 GMT

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

How to combine Variable and fields in an expression?

Fri, 03 Dec 2010 15:46:24 GMT

I tried this, this doen't work either:

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

How to combine Variable and fields in an expression?

Miguel_Angel_Baeyens — Fri, 03 Dec 2010 15:54:28 GMT

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

How to combine Variable and fields in an expression?

Fri, 03 Dec 2010 16:28:55 GMT

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

How to combine Variable and fields in an expression?

Fri, 03 Dec 2010 16:43:23 GMT

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

How to combine Variable and fields in an expression?

Fri, 03 Dec 2010 17:52:43 GMT

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!!

How to combine Variable and fields in an expression?

Miguel_Angel_Baeyens — Fri, 03 Dec 2010 18:05:29 GMT

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.

How to combine Variable and fields in an expression?

Miguel_Angel_Baeyens — Fri, 03 Dec 2010 18:21:29 GMT

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.

Re: How to combine Variable and fields in an expression?

bruno_m_santos — Thu, 11 Oct 2012 14:21:43 GMT

Hi Miguel,

I've a similar problem. I've tried everything but I'm not finding solution.

Could you give me some help please.

Thanks

http://community.qlik.com/message/268331#268331