I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?

Post the formula you're using to calculate. Is the calculation based
on the pivot table itself or the underlying source data?

On Aug 7, 11:20 am, David D <David
wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?

THe pivot table itself. I have built formulas to generate the running totals
that I need. (YTD Sales TTL TY - YTD Sales TTL LY)/YTD Sales TTL LY.

"Dave F" wrote:

Post the formula you're using to calculate. Is the calculation based
on the pivot table itself or the underlying source data?

On Aug 7, 11:20 am, David D <David
wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?

You may have built formulas but you're not pasting them here. Copy
exactly what is in your formula bar and paste it here. Else no one
can help you.

On Aug 7, 11:34 am, David D wrote:
THe pivot table itself. I have built formulas to generate the running totals
that I need. (YTD Sales TTL TY - YTD Sales TTL LY)/YTD Sales TTL LY.



"Dave F" wrote:
Post the formula you're using to calculate. Is the calculation based
on the pivot table itself or the underlying source data?

On Aug 7, 11:20 am, David D <David
wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?- Hide quoted text -

- Show quoted text -

= ('POS Units TTL' -'POS Units TTL YAGO' )/'POS Units TTL YAGO'
There are no formulas used to obtain 'POS Units TTL and/or POS Units TTL
YAGO'.
That data comes straigt into the table from the source. I must use field
settings and then options on those items source information to obtain a
running total.


"Dave F" wrote:

You may have built formulas but you're not pasting them here. Copy
exactly what is in your formula bar and paste it here. Else no one
can help you.

On Aug 7, 11:34 am, David D wrote:
THe pivot table itself. I have built formulas to generate the running totals
that I need. (YTD Sales TTL TY - YTD Sales TTL LY)/YTD Sales TTL LY.



"Dave F" wrote:
Post the formula you're using to calculate. Is the calculation based
on the pivot table itself or the underlying source data?

On Aug 7, 11:20 am, David D <David
wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?- Hide quoted text -

- Show quoted text -

A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:

A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:
I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:
Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:


A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:

I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra,

I have 11000 lines of data, with 14 attributes and 22 measurements, pulled
for 52 weeks. It is impossible to set up a rolling total within these
confines. I cannot set up multiple rolling totals within excel for 14
different attributes and have them work in conjunction with one another,
i.e., that is selecting multiple attributes at the same time, as in selecting
brand and category simultaneously and expect those rolling totals to combine
unless it is a pivot format to begin with. If you can figure this one out,
you are an excel deity. Thanks so much for taking the time to try and help me
on this.

David


"Debra Dalgleish" wrote:

I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:
Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:


A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:

I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I would look at the SUMPRODUCT function. Pivot tables aren't designed
to function in the manner you're describing.

On Aug 7, 4:46 pm, David D wrote:
Debra,

I have 11000 lines of data, with 14 attributes and 22 measurements, pulled
for 52 weeks. It is impossible to set up a rolling total within these
confines. I cannot set up multiple rolling totals within excel for 14
different attributes and have them work in conjunction with one another,
i.e., that is selecting multiple attributes at the same time, as in selecting
brand and category simultaneously and expect those rolling totals to combine
unless it is a pivot format to begin with. If you can figure this one out,
you are an excel deity. Thanks so much for taking the time to try and help me
on this.

David



"Debra Dalgleish" wrote:
I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:
Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:

A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:

I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

When I mentioned calculating outside the pivot table, I wasn't very
clear. Use the pivot table to create the running totals. Then, in a
column to the right of the pivot table, enter your (A-B)/B formula,
referring to cells within the pivot table, where the running totals are
stored.

If the layout will change, you could wrap the formula with and IF
function, to hide it if the referenced cells are empty.

David D wrote:
Debra,

I have 11000 lines of data, with 14 attributes and 22 measurements, pulled
for 52 weeks. It is impossible to set up a rolling total within these
confines. I cannot set up multiple rolling totals within excel for 14
different attributes and have them work in conjunction with one another,
i.e., that is selecting multiple attributes at the same time, as in selecting
brand and category simultaneously and expect those rolling totals to combine
unless it is a pivot format to begin with. If you can figure this one out,
you are an excel deity. Thanks so much for taking the time to try and help me
on this.

David


"Debra Dalgleish" wrote:


I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:

Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:



A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:


I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Dave. But I can't seem to be able to use function commands on a pivot
table. Thanks for your time and efforts.

David

"Dave F" wrote:

I would look at the SUMPRODUCT function. Pivot tables aren't designed
to function in the manner you're describing.

On Aug 7, 4:46 pm, David D wrote:
Debra,

I have 11000 lines of data, with 14 attributes and 22 measurements, pulled
for 52 weeks. It is impossible to set up a rolling total within these
confines. I cannot set up multiple rolling totals within excel for 14
different attributes and have them work in conjunction with one another,
i.e., that is selecting multiple attributes at the same time, as in selecting
brand and category simultaneously and expect those rolling totals to combine
unless it is a pivot format to begin with. If you can figure this one out,
you are an excel deity. Thanks so much for taking the time to try and help me
on this.

David



"Debra Dalgleish" wrote:
I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:
Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:

A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:

I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

Debra,

Thanks again. That would work if only I could place them in the necessary
place 'within' the body of the table. Thanks again. Oh, and by the way, you
are an excel deity!

"Debra Dalgleish" wrote:

When I mentioned calculating outside the pivot table, I wasn't very
clear. Use the pivot table to create the running totals. Then, in a
column to the right of the pivot table, enter your (A-B)/B formula,
referring to cells within the pivot table, where the running totals are
stored.

If the layout will change, you could wrap the formula with and IF
function, to hide it if the referenced cells are empty.

David D wrote:
Debra,

I have 11000 lines of data, with 14 attributes and 22 measurements, pulled
for 52 weeks. It is impossible to set up a rolling total within these
confines. I cannot set up multiple rolling totals within excel for 14
different attributes and have them work in conjunction with one another,
i.e., that is selecting multiple attributes at the same time, as in selecting
brand and category simultaneously and expect those rolling totals to combine
unless it is a pivot format to begin with. If you can figure this one out,
you are an excel deity. Thanks so much for taking the time to try and help me
on this.

David


"Debra Dalgleish" wrote:


I'm not sure what you mean. If the outside formulas refer to cells in
the pivot table, the results would change as the pivot table changes.

David D wrote:

Thanks Debra. Only question remains is how do I bring that info back into
report and still allow the data to change with different pivot table
selections, ie., brand, store, category...

"Debra Dalgleish" wrote:



A calculated item will use the Normal summary function, e.g. Sum, from
the other columns, not the custom calculation, like Running Total, if shown.

You could calculate the difference outside the pivot table.

David D wrote:


I have built a pivot table that contains rolling totals. When I try and get a
% change between two (2) rolling total columns, the data does not calculate
correctly. I am certain that the formula used is correct. Is there an issue
with excel that does not allow two calculated columns to be used in the
calculation of a third?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html