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)
               ,
    NUM(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.

     

    Regards,

     

    John.

  • 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.

     

    Regards

     

    Ashok

    • 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.

       

      Regards,

       

      Sunil