topic Set Expression in Variable in QlikView

Set Expression in Variable

Thu, 25 Nov 2010 16:16:03 GMT

Hello

I like to simplify an Expression, i qould like to work with variable but I don't understand the syntax, i.e. I like to have these variables:

vYTD = "AP={"<=$(=AP)"}, F_YEAR={$(=F_YEAR)}"

vPYTD = "AP={"<=$(=AP)"}, F_YEAR={$(=F_YEAR - 1)}"

vSKU = fin_SalesGroup={'SKU'}

vMPG = fin_SalesGroup={'SKU','MPG'}

vPTS = fin_SalesGroup={'PTS'}

Then in the Expression i woul like to use the variable:

=if(getfieldselections(fin_SalesGroup)='SKU',
(sum({<$(vYTD),$(vSKU)>} $(vMeasure)) /sum({<$(vPYTD),$(vSKU)>}$(vMeasure))) -1 ,

if(getfieldselections(fin_SalesGroup)='MPG',
(sum({<$(vYTD),$(vMPG)>} $(vMeasure)) /sum({<$(vPYTD),$(vMPG)>}$(vMeasure))) -1 ,

if(getfieldselections(fin_SalesGroup)='PTS',
sum({<$(vYTD),$(vPTS)>} $(vMeasure)) /sum({<$(vPYTD),$(vMPG)>}$(vMeasure))) -1

)))

How can I make this work with variables?

The original Expression which works:

=if(getfieldselections(fin_SalesGroup)='SKU',
(sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR)},fin_SalesGroup={'SKU'}>}($(vMeasure))) /sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR - 1)},fin_SalesGroup={'SKU'}>}($(vMeasure)))) -1
,if(getfieldselections(fin_SalesGroup)='MPG',
(sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR)},fin_SalesGroup={'SKU','MPG'}>}($(vMeasure)))/sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR - 1)},fin_SalesGroup={'SKU','MPG'}>}($(vMeasure)))) -1
,if(getfieldselections(fin_SalesGroup)='PTS',
(sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR)},fin_SalesGroup={'PTS'}>}($(vMeasure))) /sum({<AP={"=$(=AP)"},F_YEAR={$(=F_YEAR - 1)},fin_SalesGroup={'PTS'}>}($(vMeasure)))) -1
)))

Set Expression in Variable

Fri, 26 Nov 2010 09:23:54 GMT

Hi,

For example sum({$(v_Var)} Field) . The variable can contain more set expressions:

SET v_Var = "<fin_SalesGroup={'SKU'}>, <fin_SalesGroup={'SKU','MPG'} >" ;

-Alex

Set Expression in Variable

Wed, 01 Dec 2010 09:46:38 GMT

It almost works, but still have one problem left with this variable expression nitghmare,

MTD:

Original Expression without variable:

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

variable works:

SET vFYEAR = GetFieldSelections(F_YEAR)

SET vAP = GetFieldSelections(AP)

SET vMTD_SKU = sum({<AP={$1},F_YEAR={$2},fin_SalesGroup={'SKU'}>}$(vMeasure))

usage: $(vMTD_SKU( $(vAP),$(vFYEAR) ))

Does not work:

YTD: (now with the <= sign

Original Expression without variable:

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

SET vFYEAR = GetFieldSelections(F_YEAR)

SET vAP = GetFieldSelections(AP)

variante 1:
sum({<AP={<=$={$1}},F_YEAR={$2}, fin_SalesGroup={'SKU'}>}$(vMeasure))

variante 2:
sum({<AP={'<=$(={$1})'}, fin_SalesGroup={'SKU'}>}$(vMeasure))

variante 3:
sum({$<AP={ "<=$(={$1})" }, fin_SalesGroup={'SKU'}>}$(vMeasure))

usage: $(vYTD_SKU( $(vAP),$(vFYEAR) ))

All variants deliver a wrong result, all Accounting-Period months instead of <= AP

Any ideas whats wrong with the syntax?

Set Expression in Variable

Wed, 01 Dec 2010 09:52:06 GMT

Sorry, not greater than rather less than in a variable I meant

Set Expression in Variable

Wed, 01 Dec 2010 09:58:56 GMT

Finally I found the solution after trial and error:

remove the brackets around the variable placeholder:

sum

({$<AP={"<=$(=$1)"},fin_SalesGroup={'SKU'}>}$(vMeasure))