• 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.

Pivot Table and calculated field

H

Harry Covair

Member
Hi the forum,

Could some people explain me how Excel PT react on calculated fields. I inserted an IF condition in the calculated field dialog but the results are not those which I expected.

An example can be found on http://cjoint.com/?CEbk6EJ6Dzf

Thanks in advance


Harry
 
Good day Harry Covair


Calculated fields in a PT are a pain in the backside, the easiest way is to do this in the table and then you would have a field choice in the PT fields. If it is in the table data and the formula in the column is reading the table data as you update the table the formula will auto copy to new rows so that when you refresh the PT your PT data is updated
 
Hi bobhc,

Thank you for your quick response.

Your proposal is very wise and I will also apply.

But this does not explain why a conditional calculated field does not calculate correctly

Have a nice day

Harry
 
Good day Harry Covair


It a Microsoft thing, we all know how there software is 100% with no faults :), calculated fields do work but once you step out of Microsoft's comfort zone it gets harder and messier to make them work that's why I like to put all columns needed into the table and do the PT
 
Good day Harry Covair


I believe this is the formula to paste in to the calculated field dialog box.


=IF(Qty>50,Qty*'Unit Price'*0.9,Qty*'Unit Price')
 
Hi bobhc

If you open the link included in the first post, you will find that the formula you propose is exactly the same I used. It leads to an erroneous result and is the basis of my query on this forum.

Thank you for your answer

Have a nice weekend


Harry
 
Good day Harry


I must admit I treat PT much the same as Access tables and take a bit of time to make sure that the data tables has all the required columns (fields)in before making the PT,.....but must admit it does no all ways work as some one will want to know xyz which is not in the table and that can cause you to be tempted to use calculated fields.

There is a small difference in the two formulas


Original


IF(Qty>50;Qty *'Unit Price' *0,9;Qty *'Unit Price' )


Uploaded


=IF(Qty>50,Qty*'Unit Price'*0.9,Qty*'Unit Price')


the difference being the two ; and the comma in 0,9
 
Hi bobhc,

Thank you for your answer.

My Windows "Region and Language" settings require the semicolon in Excel formulas instead of the comma. So there is no difference between the two formulas.

Best regards


Harry
 
Hi Harry ,


There is no problem !


I opened your file , clicked on PivotTable2 , clicked on Change Data Source , and put in the proper data source address :


'PT - CalcField'!$A$1:$H$44


Narayan
 
Hi, Harry Covair!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Pivot%20Table%20and%20calculated%20field%20-%20CEbk6EJ6Dzf_sample_hc%20%28for%20Harry%20Covair%20at%20chandoo.org%29.xlsx


I've been playing around for a while with 2 new PT, changing the datasource to your included table...


I chose Vins are there were only 3 values, so combinations of them will be easy to arrange. For each combination chosed I copied and painted the pasted values of the 2 PT, one with my formula (which I didn't like as of a condition implicit) and other with your formula (which I didn't like because of the IF condition).


Look at the results, maybe they help, maybe not.


Regards!
 
@All (including you, KeyMaster)

Hi!


When pressing 'Send Post' after writing my previous comment... it dissappeared. Press F5 at browser and nothing. So I entered to Admin, and guess what? Give a look at the 2 files:

https://dl.dropboxusercontent.com/u/60558749/Post%20spammed%20-%201.png

https://dl.dropboxusercontent.com/u/60558749/Post%20spammed%20-%202.png


It seems as if the spam engine got an overdose of alcohol instead of oil.

Nobody is secure, not even Ninjas, ha ha ha...


Regards!


PS: Or maybe is "that" guy because of the photo at the park...
 
Hello the forum, Hi SirJB7,

Thank you for your answer. As always, your rational approach allows you to get to the bottom of things and your explanations are very clear. If crosstabs are very useful for syntheses, calculated fields however limit the use of function, eg vlookup is not allowed.


With thanks

Harry
 
Hi, Harry Covair!


I think that PT has serious problems when involving more than one table column in a calculated field. Even from this usually referred example:

http://www.contextures.com/excel-pivot-table-calculated-field.html

it might be derived that you could use more than one field, as we could see in your example it's not always true.


Why my theory about more than one table column? Well, as using calculated fields we couldn't do the job, I tried to uses a calculated formula instead. I got this message: it's not allowed to use references to multiple element names per field in dynamic table formulas.


Regards!
 
Good day Harry


My fault, I did not think there was such a regional diffrence between Belgium and the UK
 
You must log in or register to reply here.
Back
Top