10 Replies Latest reply: Jul 15, 2016 7:20 AM by Ruben Marin RSS

    Number formatting not working

    Sowmya M

      Hi Guys,

       

      I am using a calculated field that needs to change based on toggle between total and average.

       

      I am trying to get the format dynamically for totals in million and average in thousands. The formatting is not working.

      Please find below the code i am using.

      - Aggregate is the toggle between total and average using a variable extension

      - i use the year code is to select the max date always.

       

      //Code

      if(Only({$<Year={$(=Max(Year))}>}Aggregate)=1,

         num((Sum({$<Year={$(=Max(Year)}>}Contribution)/1000000),'#.##0,##M'),

       

      if(Only({$<Year={$(=Max(Year)}>}Aggregate)=2,

         num((Sum({$<Year={$(=Max(Year))}>}Contribution)/

         Count({$<Year={$(=Max(YEar)}>}[ACCT])),'#,##0.00K')))

       

       

      Any help would be appreciated.

        • Re: Number formatting not working
          Michael Granillo

          try

           

          if(Only({$<Year={$(=Max(Year))}>}Aggregate)=1,

            num((Sum({$<Year={$(=Max(Year))}>}Contribution)/1000000),'#.##0,##M')& '',

           

          if(Only({$<Year={$(=Max(Year))}>}Aggregate)=2,

            num((Sum({$<Year={$(=Max(Year))}>}Contribution)/

            Count({$<Year={$(=Max(Year))}>}[ACCT])),'#,##0.00K')& ''))

            • Re: Number formatting not working
              Sowmya M

              Hi Michael,

               

              This works great!! But in cases  where i need to add two set expressions it doesn't work. Am I doing something wrong here?

               

              if(Only({$<Year={$(=Max(Year))}>}Aggregate)=1,

                 num((Sum({$<Year={$(=Max(Year))}>}CONTRIBUTION)+

                 Sum({$<Year={$(=Max(Year))}>}WITHDRAWL))/1000000,'#,##0.00M')&'',

               

              if(Only({$<Year={$(=Max(Year))}>}Aggregate)=2,

                 num((Sum({$<Year={$(=Max(Year))}>}CONTRIBUTION)+Sum({$<SnapshotYr={$(=Max(YEAR)}>}WITHDRAWL))/

                 count({$<Year={$(=Max(Year))}>}ACCT),'#,##0.00')&''))

                • Re: Number formatting not working
                  Sunny Talwar

                  Syntax looks right to me...would you be able to share screenshot to show that it isn't working?

                    • Re: Number formatting not working
                      Sowmya M

                      You can see the number 331 on the right hand side. The number dissappears when i add the &' ' formatting

                        • Re: Number formatting not working
                          Sowmya M

                          Even in cases like this, the toggle for total and average does not work.It simply maintains the total value even for average.

                          Maybe the way i am using the sum and + functions are not really right?

                            

                          if(Only({<Year= >} valuelist('Net','Total')) = 'Net',

                                        if (Only({<Year= >}Aggregate)=1, (Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL)),

                                        if (Only({<Year= >}Aggregate)=2,((Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL)))/count({<Year=>}ACCT)),

                                        Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL))))

                                   

                          )

                          • Re: Number formatting not working
                            Sunny Talwar

                            Is it may be due to the size of the object? Have you tried resizing the object to be a little larger (try increasing the length)

                              • Re: Number formatting not working
                                Sowmya M

                                No that doesn't work.

                                 

                                It's strange that it works well in a field like this:

                                 

                                num(Count({$<Year={$(=Max(Year))},Assets={">0"}>}ACCT)/1000,'#,##0K')& ''"

                                 

                                but not in

                                 

                                if(Only({<Year= >}valuelist('Acct','Ctrb'))='Acct', num(count({<year= , ASSETS={">0"}>}ACCT)/1000,'#,##0K')&''"

                                 

                                It looks to me like using a valuelist is causing this problem because everywhere i have a valuelist it doesnt work.

                              • Re: Number formatting not working
                                Ruben Marin

                                Hi Sowmya, I think you can't set a dynamic numeric format for KPI objects, if you set the number to 'Auto' it will automatically add 'k, M, B...'.

                                 

                                You can try to simulate the object using a text box, there you can set number format dynamically for each expression and you can set a different size and/or color for each expression used.

                                 

                                EDIT: Well, seems you can. I tested Num(1000, '0,0k') and didn't worked but Num(Sum(A), '0,0k') worked.