Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis, dynamic number formatting and commas

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.

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

11 Replies
sunny_talwar

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

Not applicable
Author

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.

adamdavi3s
Master
Master

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

adamdavi3s
Master
Master

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

adamdavi3s
Master
Master

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

sunny_talwar

Nope, can't get it to work

adamdavi3s
Master
Master

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!

Not applicable
Author

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?

marcus_sommer

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