Discussions
Categories
 17.9K All Categories
 3.4K Industry Applications
 3.4K Intelligent Advisor
 73 Insurance
 537.3K OnPremises Infrastructure
 138.6K Analytics Software
 38.6K Application Development Software
 6K Cloud Platform
 109.6K Database Software
 17.6K Enterprise Manager
 8.8K Hardware
 71.2K Infrastructure Software
 105.3K Integration
 41.6K Security Software
BI Answers pivot problem
iv*689460*vo
Member Posts: 7
Hello everyone,
i'm working with a pivot table in Answers and have a number of columns with some numeric values (measures) with a total under the rows (after sum).
However, I don't want to show values but percentage for each value in the column based on the total for that column.
The problem is that in some of the columns i have only value 0 (zero), so the sum of that column is also 0 (zero) and the percentage can not be calculated (it is division 0/0 which is infinity/undetermined) and the pivot gets no values for these columns (it's just empty, no null no error, nothing).
I would like not to have these "empty" columns but "0%" as a value. What is a solution for this? Can I, somehow, set a property or something that would say "if there are empty cells, put some value that i want in there"?
To illustrate this, I put together this small table:
col1 col2 col3
11 0 15
...
7 0 13
total(e.g.) 58 0 44
In this case, when I convert the values to percentage e.g. 11/58 makes ~19% for the first cell of the col1, but for values in col2 I have 0/0 since the total is also zero, as it is for each value.
any help is welcome, thanks
Edited by: [email protected] on Nov 11, 2009 7:11 AM
Edited by: [email protected] on Nov 11, 2009 7:15 AM
Edited by: [email protected] on Nov 11, 2009 7:15 AM
Edited by: [email protected] on Nov 11, 2009 7:16 AM
i'm working with a pivot table in Answers and have a number of columns with some numeric values (measures) with a total under the rows (after sum).
However, I don't want to show values but percentage for each value in the column based on the total for that column.
The problem is that in some of the columns i have only value 0 (zero), so the sum of that column is also 0 (zero) and the percentage can not be calculated (it is division 0/0 which is infinity/undetermined) and the pivot gets no values for these columns (it's just empty, no null no error, nothing).
I would like not to have these "empty" columns but "0%" as a value. What is a solution for this? Can I, somehow, set a property or something that would say "if there are empty cells, put some value that i want in there"?
To illustrate this, I put together this small table:
col1 col2 col3
11 0 15
...
7 0 13
total(e.g.) 58 0 44
In this case, when I convert the values to percentage e.g. 11/58 makes ~19% for the first cell of the col1, but for values in col2 I have 0/0 since the total is also zero, as it is for each value.
any help is welcome, thanks
Edited by: [email protected] on Nov 11, 2009 7:11 AM
Edited by: [email protected] on Nov 11, 2009 7:15 AM
Edited by: [email protected] on Nov 11, 2009 7:15 AM
Edited by: [email protected] on Nov 11, 2009 7:16 AM
Answers

For an empty null column to be displayed zeroes you can use IF NULL condition.
If Null(column1,0.0) It will add zeroes or zero percentages(what ever you want) when there is a null cell.
Hope it helps
Thanks
Prash
Edited by: Prash11 on Nov 11, 2009 11:16 AM 
Were you able to solve the problem? If so mark the thread as correct and kindly close the thread
Thanks
Prash 
Sorry for not answering, I didn't get a chance to...
Anyway, ifnull function does not solve the problem because I don't have nulls, but zeros when the percentage is calculated..
I'll try something else...
thank you
This discussion has been closed.