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.

[sunny_talwar]

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

[#3]

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]

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]

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]

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]

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!

[#9]

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 · Accepted solution]

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