• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

calculated item bizarre result

B

becha

New Member
could anyone provide some help on pivot tables' calculated items?


the formula is giving me some really bizarre results


i can upload the file in question to rapidshare so that it is easier to understand what im talking about


any good samaritan, please let me know

thanks so much to all of ya
 
Actually, I'm an idiot. It was in your post title and I didn't realize it.


Post your data, we'll have a look.
 
dan_I, thanks a lot. i will upload the file today. i'll confimr today with a post inlcuding the questions on the matter.
 
dan_I, here is the link to the file

http://rapidshare.com/files/442004504/bizarre_result_on_calculated_items.xlsx


here are my concerns/doubts:


1) In the proyeccion 2011 1 tab, for a pivot table with a report filter item called Portatiles, i added a calculated item.

The calculation was to take the items for Product PS3 and substract 10.

It worked however i noticed the calculation was done for ALL values besides PS3 ones. The report filter was overriden for the new calculated item!

Is this normal?

Does this mean that after applying a calculated item, the user has to rearrange the filters to show only the data that was intended???


2) Is it possible to do a calculated item pointing to additional items not part of the original field in which the formula is being 'inserted on'?


Because in tab proyeccion 2011 2, i was able to input a formula (an error message wasnt returned by excel avoiding me to enter the formula) however the result's quite bizarre(!?)


After looking at the case above, do you have any thoughts or comments?

Better yet, can you share the Rules to keep in mind when working with calculated items, based in your experience?


I added the formula listing so you can read what the syntax was for both calc items


Dan_I, thanks again; for your interest and time
 
Ok, lemme make sure I understand the first part:


So your problem is all of the -10's in place of 0's?
 
hi dan_l


yes!


not only did the calculated item disregarded the report filter in place that pointed out to specific items (Portatiles items of the Category field) to show in the pivot table; also the formula added "new items" (not existent in the source data) to all of the original products!


The fucntion added Titles to PS3 that belong to the Xbox Product and vice versa


Is this normal?

Does this mean that after applying a calculated item, some items non existent in the source data will be replicated among other items?


also does the effect of the formula throws out previous filters in place meaning that the user has to rearrange them to show only the data that was intended???


thanks for your time allocated on this


PS.- cell A65 contains the issue i talked about on Matter number 2, posted yesterday
 
I'm sorry Becha. I didn't notice that you had replied. You can apply an if statement to your calculated field ie: if(column=0,0,column+10) to avoid the issue.
 
thanks Dan_I; excuse me, i dont follow; you mention calculated field when the issue i had was with calculated items; well 2 issues specifically


maybe you can take a look at the file one last time and see what i meant...


i apologize if im a little bit lost here...


sincerely grateful for your time
 
thanks Dan_I; excuse me, i dont follow; you mention calculated field when the issue i had was with calculated items; well 2 issues specifically


maybe you can take a look at the file one last time and see what i meant...


i apologize if im a little bit lost here...


sincerely grateful for your time
 
becha,


Your calculated items should be controlled by a calculated field, one that was initially defined by some formula like:

column + 10


To prevent the error, you need to change this as Dan_I suggested to:

=IF(column = 0,0,column+10)
 
You must log in or register to reply here.
Back
Top