topic Re: Set analysis, dynamic number formatting and commas in QlikView

Set analysis, dynamic number formatting and commas

Tue, 14 Mar 2017 09:33:16 GMT

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.

Re: Set analysis, dynamic number formatting and commas

sunny_talwar — Tue, 14 Mar 2017 10:24:50 GMT

A qvw example might help us to play around with it, so I think providing one would be a good idea

Re: Set analysis, dynamic number formatting and commas

Tue, 14 Mar 2017 11:31:01 GMT

As per your request, you will find an example attached. I hope it is good enough. There are 4 expressions in my table and the fourth is the one that I'm having trouble with.

Re: Set analysis, dynamic number formatting and commas

adamdavi3s — Tue, 14 Mar 2017 11:33:51 GMT

regarding the number formatting, I used this extensively and from memory it doesn't like set analysis, you need to store the formula with the set analysis as another variable and use this

Re: Set analysis, dynamic number formatting and commas

adamdavi3s — Tue, 14 Mar 2017 11:41:04 GMT

actually looking at it again, I don't think I ever got it to work, I had to just use the if statement in the expression if I needed to use set analysis

Re: Set analysis, dynamic number formatting and commas

adamdavi3s — Tue, 14 Mar 2017 11:46:00 GMT

stalwar1‌ might be able to work some aggr magic to make this work, but I can't find the right syntax

Re: Set analysis, dynamic number formatting and commas

sunny_talwar — Tue, 14 Mar 2017 11:52:16 GMT

Nope, can't get it to work

Re: Set analysis, dynamic number formatting and commas

adamdavi3s — Tue, 14 Mar 2017 11:55:26 GMT

As long as it's not just me then!

I'm pretty sure that was the conclusion I came to before I am afraid, however the number formatting formula itself is great, but not ideal when you use it within an expression as you need to update all expressions with any changes which kind of defeats the point!

Re: Set analysis, dynamic number formatting and commas

Wed, 15 Mar 2017 08:42:48 GMT

Thank you guys for your answers. Looks like I won't get it my way .

I'll use a workaround solution by dividing my expression by a variable that's equal to 1, 1000 or 1000000. The only issue is that I won't have a digit for when I want millions but I can't see any other easy solution.

Could you also confirm that there is no workaround using the Round() function?

Re: Set analysis, dynamic number formatting and commas

marcus_sommer — Wed, 15 Mar 2017 12:19:33 GMT

There is unfortunately no way to mask a comma within a variable-parameter but there are more or less expensive ways to bypass this limitation. Most common would be to use a different char for it and to replace it within the variable-call per $-sign expansion whereby this might be not enough because possible single-quotes within the string will cause further challenges and needs also to be replaced.

With this in mind you could create the expression-variable like:

if(vSlider=1, num($(=replace(replace('$1', ';', ','), '#', chr(39)))/1000000,'# ##0,0')

,if(vSlider=2, num($(=replace(replace('$1', ';', ','), '#', chr(39)))/1000,'# ##0')

,num($(=replace(replace('$1', ';', ','), '#', chr(39))),'# ##0')))

and a parameter-variable like:

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

and you will get:

- Marcus

Re: Set analysis, dynamic number formatting and commas

Thu, 16 Mar 2017 13:25:09 GMT

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.

Re: Set analysis, dynamic number formatting and commas

marcus_sommer — Thu, 16 Mar 2017 14:57:56 GMT

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