topic Re: Variable Function is not accepting more than one set operator parameters in QlikView

Variable Function is not accepting more than one set operator parameters

qlik_venu — Thu, 14 May 2020 21:28:09 GMT

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

Re: Variable Function is not accepting more than one set operator parameters

rwunderlich — Fri, 15 May 2020 00:11:34 GMT

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

Re: Variable Function is not accepting more than one set operator parameters

marcus_sommer — Fri, 15 May 2020 11:10:26 GMT

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

Re: Variable Function is not accepting more than one set operator parameters

qlik_venu — Mon, 25 May 2020 03:36:33 GMT

Hi,

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

Regards,

Venu

Re: Variable Function is not accepting more than one set operator parameters

qlik_venu — Mon, 25 May 2020 21:48:26 GMT

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

Re: Variable Function is not accepting more than one set operator parameters

qlik_venu — Mon, 25 May 2020 22:01:12 GMT

The expression I am now using,

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