If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Display ranges in a Pivot
Hi all,
I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help |
#2
|
|||
|
|||
Display ranges in a Pivot
Just wanted to explain a little more.
Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both groups. Is it possible to take a value in a column and calculate and then return multiple columns for multiple ranges? example baseCol values 0-9 10-20 21-30 31-40 etc 15 1 20 1 6 1 33 1 9 1 28 1 Thanks for any help and suggestions "NetNewbie" wrote: Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help |
#3
|
|||
|
|||
Display ranges in a Pivot
Hi Netnewbie
What you need is simple - just use your pivot table's grouping feature. Try this: 1. From inside your Pivot Table report, right click on any of the values in the IncomeRange field. 2. Click Group and Show Detail, then click Group. 3. The Grouping dialog box appears. From here, notice these amounts will pre-populate with default "Starting at", "Ending at", and "By" values, where "Starting at" will = the minimum value in your IncomeRange field, and "Ending at" will = the maximum value in your IncomeRange field, and "By" represents the desired increment of the groupings 4. Modify the values as needed, or click OK to accept the defaults. Note these groupings will update along with your data, the same way the rest of the pivot table does ------ XSzil Spreadsheet Heroine "NetNewbie" wrote: Just wanted to explain a little more. Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both groups. Is it possible to take a value in a column and calculate and then return multiple columns for multiple ranges? example baseCol values 0-9 10-20 21-30 31-40 etc 15 1 20 1 6 1 33 1 9 1 28 1 Thanks for any help and suggestions "NetNewbie" wrote: Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help |
#4
|
|||
|
|||
Display ranges in a Pivot
Thank you for your response. It does not necessarily work in my case, since
the goruping option is only available if the filed is in the RowLabels section. I needs them as individual columns in the values section. I did it using the SQL query. "x szil" wrote: Hi Netnewbie What you need is simple - just use your pivot table's grouping feature. Try this: 1. From inside your Pivot Table report, right click on any of the values in the IncomeRange field. 2. Click Group and Show Detail, then click Group. 3. The Grouping dialog box appears. From here, notice these amounts will pre-populate with default "Starting at", "Ending at", and "By" values, where "Starting at" will = the minimum value in your IncomeRange field, and "Ending at" will = the maximum value in your IncomeRange field, and "By" represents the desired increment of the groupings 4. Modify the values as needed, or click OK to accept the defaults. Note these groupings will update along with your data, the same way the rest of the pivot table does ------ XSzil Spreadsheet Heroine "NetNewbie" wrote: Just wanted to explain a little more. Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both groups. Is it possible to take a value in a column and calculate and then return multiple columns for multiple ranges? example baseCol values 0-9 10-20 21-30 31-40 etc 15 1 20 1 6 1 33 1 9 1 28 1 Thanks for any help and suggestions "NetNewbie" wrote: Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help |
Thread Tools | |
Display Modes | |
|
|