11 Replies Latest reply: Mar 16, 2017 10:57 AM by Marcus Sommer RSS

    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.