topic Formatting numbers in a pivot table in QlikView

Formatting numbers in a pivot table

Sunil_Kenth — Sun, 04 Mar 2012 20:18:59 GMT

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

Anonymous — Mon, 05 Mar 2012 00:53:31 GMT

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

Anonymous — Mon, 05 Mar 2012 04:53:28 GMT

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 — Mon, 05 Mar 2012 08:49:27 GMT

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