Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Variable with parameter not working in expression

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2017-10-03
02:09 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Variable with parameter not working in expression

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.

2,526 Views

1 Solution

Accepted Solutions

swuehl

MVP

2017-10-03
03:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This might explain the magic of the leading equal sign:

Maybe try (without the leading equal sign variable definition)

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

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

2 Replies

swuehl

MVP

2017-10-03
03:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This might explain the magic of the leading equal sign:

Maybe try (without the leading equal sign variable definition)

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

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

Not applicable

2017-10-04
05:43 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,726 Views