topic Re: Dollar Sign Expansion + Variable + Set Analysis in QlikView

Dollar Sign Expansion + Variable + Set Analysis

Fri, 17 Jun 2016 16:48:59 GMT

equals a messy qlikview expression burrito.

I have an expression variable vYTDLeads which houses the expression

Sum({$<FISYR={$(#vFisYr)},WKNO={$(#vPWKNO)}>}Leads)

I am looking to insert another set modifier in the form of a $ expansion that is determined on a variable.

the variable would be: vCMSTotalExp

vCMSTotalExp =

If(vCMSTotal = 1,

'ZIP={$(=99999)}',

'ZIP-={$(=99999)}'

when inserting EITHER of Zip set analysis expressions into the Sum...Leads) expression, it works as expected, however, I'd prefer if the expression runs on a button set variable rather than doing an if statement in the vYTDLeads expression.

Any insight in the syntax to get the expreission to work similar to this:

Sum({$<FISYR={$(#vFisYr)},$(vCMSTotalExp),WKNO={$(#vPWKNO)}>}Leads)

Re: Dollar Sign Expansion + Variable + Set Analysis

sunny_talwar — Fri, 17 Jun 2016 17:41:39 GMT

Not sure I understand your intention, do you want to use this ->

Sum({$<FISYR={$(#vFisYr)},$(vCMSTotalExp),WKNO={$(#vPWKNO)}>}Leads)

or are you looking for work arounds?

Re: Dollar Sign Expansion + Variable + Set Analysis

Fri, 17 Jun 2016 17:49:42 GMT

Based on previous experience,

Sum({$<FISYR={$(#vFisYr)},$(vCMSTotalExp),WKNO={$(#vPWKNO)}>}Leads)

SHOULD work, but doesnt. And i'm not sure why. It gives me errors in expression in the editor.

Re: Dollar Sign Expansion + Variable + Set Analysis

sunny_talwar — Fri, 17 Jun 2016 18:06:06 GMT

Can you try this:

If(vCMSTotal = 1, 'ZIP={99999}', 'ZIP-={99999}')

Re: Dollar Sign Expansion + Variable + Set Analysis

Fri, 17 Jun 2016 18:12:14 GMT

Same error.

Re: Dollar Sign Expansion + Variable + Set Analysis

sunny_talwar — Fri, 17 Jun 2016 18:26:42 GMT

Well the expression editor will continue to show an error, because it doesn't understand the variable. But you should still be able to get the output as expected. Have you tried clicking okay to see if the output is coming as expected?

Re: Dollar Sign Expansion + Variable + Set Analysis

Fri, 17 Jun 2016 18:38:38 GMT

I have tried clicking ok, with the variable if statement as you posted earlier, and

Sum({$<FISYR={$(#vFisYr)},$(vCMSTotalExp),WKNO={$(#vPWKNO)}>}Leads)

as the set analysis expression.

I am getting the standard ‘error in set modifier expression’

Do you think it would help if I adjust the If statement to exlude zip?

So the if statement would be:

If(vCMSTotal = 1,

‘=’ , ‘-=’)

That way the set modifier has a dimension to work off of? Is this a viable work around?

Re: Dollar Sign Expansion + Variable + Set Analysis

swuehl — Fri, 17 Jun 2016 23:27:46 GMT

double check what is actually replaced by your dollar sign expansion of $(vCMSTotalExp).

What you want to have is either

ZIP={99999}

ZIP-={99999}

You don't want the if() statement put into the set expression

If(vCMSTotal = 1, 'ZIP={99999}', 'ZIP-={99999}')

Hence you need to define your variable with a leading equal sign, so it gets evaluated and the result is used for the dollar sign expansion.

=If(vCMSTotal = 1, 'ZIP={99999}', 'ZIP-={99999}')

The Little Equals Sign

The Magic of Variables

The Magic of Dollar Expansions

Double check the result of the evaluated variable e.g. in a variable input box.

I then would suggest that you create a straight table chart with no dimension and your expression

Sum({$<FISYR={$(#vFisYr)},$(vCMSTotalExp),WKNO={$(#vPWKNO)}>}Leads)

hover with the mouse over the expression header, you should see the expression with all DSE expanded.

Carefully check that the full expression is like requested.