3 Replies Latest reply: Mar 5, 2012 3:49 AM by Sunil Kenth RSS

    Formatting numbers in a pivot table

    Sunil Kenth

      Hi All,


      I have a pivot table where each line for the expression called 'Actual'  need to be formatted differently based on the source data.  For example, one set of data that i am pullng in needs to be formatted in % and another set of data needs to fomatted in currency.  In my source excel spreadsheets i have formatted the data as i want it to appear in QV, and in the number formatting settings in the chart properties I have left the setting as expression default. 


      What i am getting is all the data appearing as % which doesnt make sense.


      Attached is the application.

        • Formatting numbers in a pivot table
          John Anderson

          Hi SKH,


          none of your values make sense as a percentage.  which ones are suppose to be percentage?


          I think the solution to your problem is with the Expressions.  take Actual for a start. 


          You could use a nested IF statemen like this.


          IF(KPIName = 'Pick Accuracy',NUM(sum({$<DataType={'Actual'}>}Value),'##.##%')
          IF(KPIName = 'Store Delivery On Time',sum({$<DataType={'Actual'}>}Value)


          You could make this even better by adding in another field element at time of load where you identify the value as '%', '#' and so on.  these would indicate the output should be formated as a percentage or number. 


          Let me know how you go.





          • Re: Formatting numbers in a pivot table
            Ashok Chandran

            Hi skh,


                 Here i attached your qv file with multiple number format using the expression.


                 Hope this will helpful you to understand about the format using the expression.





              • Re: Formatting numbers in a pivot table
                Sunil Kenth

                Thank you both for your responses.  I would've marked them both as correct but there is only the option to mark one response as correct!  As Ashok's had the example attached, i thought this would be helpful for other too and therefore marked this as correct.  John, i like your side of adding in and additional column to hold the unit of measure.