Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Variable Function is not accepting more than one set operator parameters

Hi,

I am using a Variable defined function for calculaitng the Million, Thousand or number format. this part below is defined in the script.

SET vScaleNumbers_EU = if( fabs($1)> 1000000 and $2=2, num($1/1000000 ,'# ##0,00 M',',',' '),
if( fabs($1)> 1000 and $2=2, num($1/1000 ,'# ##0,00 K',',',' '),
NUM($1,'# ##0','',' ')));

SET vScaleNumbers_US = if(fabs($1)> 1000000 and $2=2, num($1/1000000 ,'#,##0.00 M','.',','),
if(fabs($1)> 1000 and $2=2, num($1/1000, '#,##0.00 K' ,'.',','),NUM($1,'# ##0','',' ')));

And in the expresions in the chart, I am using,

$(vScaleNumbers_$(vCurrencyFormat)( sum ( {<DATE_KEY={'$(vDateRange_Current_Full)'}>}Sales) , $(vDisplayNumberFormat) )

The vCurrencyFormat defines where its US or EU.
and $1 paremeter is = sum ( {<DATE_KEY={'$(vDateRange_Current_Full)'}>}Sales)
and $2 parameter is = vDisplayNumberFormat, which defines whether the number needs to be whole number ( 2) or formatted (1).

The above works fine when we have just one set analysis parameter. As soon as I add the other parameters the expression fails.

$(vScaleNumbers_$(vCurrencyFormat)( sum ( {<DATE_KEY={'$(vDateRange_Current_Full)'}, [Month]={'*'},[Cal Quarter]={'*'} >}Sales) , $(vDisplayNumberFormat) )

This is because the variable function assume comma seperator in the Sum function as the next $2 parameter.

Can you please suggest on this.

thanks,
Venu

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

In some cases you could bypass the comma-as-parameter handling from the variables by adjusting the syntax to a none-comma approach. Multiple conditions within a set analysis could be also written in this way:

sum ( {<DATE_KEY={'$(vDateRange_Current_Full)'}> * < [Month]={'*'}> * < [Cal Quarter]={'*'} >}Sales)

and by multiple values within a condition they could be defined like:

Field = {(value1|value2|value3)}

- Marcus

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

This is a longstanding irritant in the variable parameters.  Comma will always be interpreted as a parm separator, there is no way to pass it as data. 

The only workaround I know of is to use a proxy character like "|" in place of the comma and then use replace() in the expression to put the comma back in.

-Rob

Highlighted
MVP & Luminary
MVP & Luminary

In some cases you could bypass the comma-as-parameter handling from the variables by adjusting the syntax to a none-comma approach. Multiple conditions within a set analysis could be also written in this way:

sum ( {<DATE_KEY={'$(vDateRange_Current_Full)'}> * < [Month]={'*'}> * < [Cal Quarter]={'*'} >}Sales)

and by multiple values within a condition they could be defined like:

Field = {(value1|value2|value3)}

- Marcus

View solution in original post

Highlighted
Contributor III
Contributor III

Hi,

Thanks for the update.!! This is working fine.

 

Regards,

Venu

Highlighted
Contributor III
Contributor III

Hi @marcus_sommer ,

I had an issue using the asterixis. I wanted the sum function to remain unaffected by any selections to the "Month" field. But using the asterixis as you had suggested resulted in selections in the "Month" affecting the expression.  I guess the * intersects the month which gets added to the first set of the date range key filter.

I used the + operator to add the sets instead of the asterix. * and this seems to do the union of the sets with the month not applied to the set. This seems to work fine right now.

But do you see any issues in using the + which I may have missed.  Thanks for guiding this unique way of applying sets.

Regards,

Venu

 

 

Contributor III
Contributor III

The expression I am now using,

=$(vScaleNumbers_$(vCurrencyFormat)( Sum({$< _Z_DATE_KEY={"$(vDateRange_Current_Full)"}> +<Month => }$(vExpSelection)), vDisplayNumberFormat))