Set analysis, dynamic number formatting and commas

[#1]

Hi there,

I am working with pivot and straight tables and need to be able to dynamically change to number format, e.g. there is a slider allowing the user to switch from € to k€ (thousand) and then to M€ (million). The € symbol is not needed but makes it easier to understand my need.

I have achieved this by following the instructions in this thread (Dynamically changing number formats?) and it works wonderfully until I mix some set analysis in it.

So far, I have created a variable in script that looks like this :

SET vScaleNumber=if(vSlider=1, num($1/1000000,'# ##0,0')

,if(vSlider=2, num($1/1000,'# ##0')

,num($1,'# ##0')

));

It is intended to show one digit only when in million mode and none otherwise. There is a slider allowing the user to change the variable vSlider from 1 to 3 and letting him chose how he wants to display the numbers.

In my tables, I can use it like :

$(vScaleNumber(sum({<unit={'AA'}>} sales)))

and it works how it should. But as soon as I add an parameter to my set analysis (  $(vScaleNumber(sum({<unit={'AA'},Country={'BB'}>} sales)))  ), it won't work anymore. I carefully read this thread ( Comma problem (,) workaround for dollar sign expansion with parameters ) but can't make it work.

Furthermore, I have stored most of my set analysis in variables so that I have a variable vSetAna1 that looks like unit={'AA'},Country={'BB'} which I should be able to use in an expression like $(vScaleNumber(sum({<$(vSetAna1)>} sales))) which does only work when there is no comma in my set analysis.

In the end, I also might want to mix set analysis stored in multiple set analysis so that my expression looks like $(vScaleNumber(sum({<$(vSetAna1),$(vSetAna2)>} sales))) or $(vScaleNumber(sum({<$(vSetAna1)>+<$(vSetAna2)>} sales))).

I would welcome any help to solve my issue and if need be, I can try to provide a .qvw example .

PS : My issue is not particularly related to number formatting so if you come up with a better title, I'll change it.

[#2]

Thank you Marcus, that worked like a charm, even with nested variables (like vSetAna1 = unit={$(=vFilterUnit)};Country={$(=vFilterCountry)} ).

Are there other characters beside comma and single/double quotes that need to be replaced? Though I haven't encountered one yet.

Since it is also semi-related, I want to store my variables somewhere because it is quite tedious to manage them in the Variable Overview. Do you think Excel is future-proof? Storing them directly in the script seems risky.

[marcus_sommer]

The # sign could be used by format-functions or to interpret a variable-value but the semi-colon seems to be only used within the script and should therefore not bother the qlik-syntax within expressions. But both chars could be occur within fieldnames, too but if you double the chars like:

unit={##AA##};;colour={##yellow##}

and adjusted the replacing appropriate you could minimize the risk that there are such overlappings.

Of course you could also choose some exotic chars from the extended ascii- or even the uni-code-area but they aren't quite convenient by the usage, for example: ╣for ALT+185.

To centralize the variables on an external storage could really help to manage and maintain them. Quite common are include-variables for it or even more convenient would be excel. Further loading the expression-variables per loop from excel avoids problems with $-signs within them. For a how to see here: http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/‌

- Marcus