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

    Set analysis, dynamic number formatting and commas

    Aaaa Aaa

      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.