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.