Variable Function is not accepting more than one set operator parameters

[qlik_venu]

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

[rwunderlich]

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

[marcus_sommer · Accepted solution]

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

[qlik_venu]

Hi,

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

Regards,

Venu

[qlik_venu]

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

[qlik_venu]

The expression I am now using,

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