Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
Luminary
Luminary

How to combine Variable and fields in an expression?

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

How to combine Variable and fields in an expression?

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

How to combine Variable and fields in an expression?

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?

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

How to combine Variable and fields in an expression?

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

How to combine Variable and fields in an expression?

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

How to combine Variable and fields in an expression?

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?

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?

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.