Variable with parameter not working in expression

Wed, 25 Nov 2020 16:16:04 GMT

Dear Qlikers,

A newbie here trying to figure out why this simple statement wont work.

I have a simple expression:

=1-(Sum({1<Month ={'Oct-17'}>} Breaches))

/Sum({1<CalMonthYear2={'Oct-17'}>} Pathways)

which works fine.

Then in same expression I put a simple variable which is:

vTest = Oct-17

like so;

=1-(Sum({1<Month ={"$(vTest)"}>} Breaches))

/Sum({1<CalMonthYear2={'Oct-17'}>} Pathways)

also works great

Then in same expression I put a slightly more complex variable which is:

vTest2 = Text(Month(Today())) & '-' & Text(right(Year(Today()),2))

(which outputs the same figure 'Oct-17' and works fine called from a text box) but in the expression

like so;

=1-(Sum({1<Month ={"$(vTest2)"}>} Breaches))

/Sum({1<CalMonthYear2={'Oct-17'}>} Pathways)

but this only works if '=' is placed in front of formula in Variable Overview. Anyway it works so thats fine.

However I then have an even more complex function or 'parameter' variable which outputs exactly the same figure of 'Oct-17':

vTest3 =

if($1=0,

Text(Month(Today())) & '-' & Text(right(Year(Today()),2)),

Text(Month(addMonths(Today(),$1)))

& '-' &

if(

num(Month(addMonths(Today(),$1)),'00') <4,

Text(right(Year(addYears(Today(),1)),2)),

Text(right(Year(Today()),2))

)

works great in a normal text box but in the above expression i.e.

=1-(Sum({1<Month ={"$(vTest3(0))"}>} Breaches))

/Sum({1<CalMonthYear2={'Oct-17'}>} Pathways)

this also refuses to work.

Can someone please help me as I'm pulling out my hair. I'm a sql guy and this is a little weird for me lol

Many thanks for your help in anticipation.

Re: Variable with parameter not working in expression

swuehl — Tue, 03 Oct 2017 19:26:37 GMT

This might explain the magic of the leading equal sign:

The Magic of Variables

Maybe try (without the leading equal sign variable definition)

=1-(Sum({1<Month ={"$(=$(vTest3(0)))"}>} Breaches))

/Sum({1<CalMonthYear2={'Oct-17'}>} Pathways)

Re: Variable with parameter not working in expression

Wed, 04 Oct 2017 09:43:01 GMT

Actually Stefan,

As usual you are a genius. I had an extra '=' at the beginning of the formula in the Variable Overview where I assigned the formula, and that stopped it from working. Yes I re-read your post telling me to rid the equals sign there too duh.

Thank you so much

topic Re: Variable with parameter not working in expression in QlikView

Variable with parameter not working in expression

Re: Variable with parameter not working in expression

Re: Variable with parameter not working in expression