Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivot Tables - Calculated Value

Posted on 2011-02-22
15
Medium Priority
?
645 Views
Last Modified: 2013-11-05
I have a Pivot table that has a variable (set to Count) that has columns over a 10 year period.  The last year/period represents only a potion of the year so I would like to "annualize" the last year/period data.  I want to do this by dividing the last years count value by the ratio of days currently represented in the year to date (e.g., .141 for date in February).  I've looked at calculated variables but it does not appear that you can work with a count value and modify it as I've described.  I reuse the same pivot table for different data sets that (using VBA) that prohibits me from putting the pivot table value in a normal Excel cell and doing the calculations.  I pull the values directly from the pivot table to a data select on an Excel chart.  
0
Comment
Question by:jaustinr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34957613
Can you upload a non-confidential sample of data?

Dave
0
 

Author Comment

by:jaustinr
ID: 34957807
I will look at how I might display the PT.  The underlying data is really not an issue and should have no impact on the solution.  I simply have a variable in the PT that I've set to Count.  The PT summarizes the number of rows associated with that variable as a number (count).  I have a count for each of the 10 years of data I'm analyzing.  Since I don't have a full year for 2011 I want to correct the 2011 count by dividing by a correction factor.  As I noted, I cannot pull the 2011 count to a cell and do the calculations as the overall automated analysis procedure requires I pull the data directly to an Excel chart process.  The exact PT path for each chart value is inserted into the "select data" function of the Excel chart.  I need to find a way to correct one of the values before plotting.  I've tried to add calculations into the select data area but it isn't compatible with this type function.  I've also looked at using a calculated value but don't see a way to use a count (of a variable) in the calculation.

jaustinr
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34958125
I understand, but it would be a simple think for you to create a simple problem I can help you to solve, as opposed to me straining my brain to simulate that.  I'm willing to help you solve the problem - are you willing to create a sample dataset so I can help you solve the problem?

Dave
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1000 total points
ID: 34958293
Ok - I took a bit of time and thought about the problem.  Then, I created my own sample dataset - so I hope this suits.  Sometimes by creating a sample (data does NOT matter, that's correct - but pictures in the mind saves thousands of words - or the 339 you used to describe the problem :)

Anyway, I hope this works for you.  I created a simple dataset with two people, and salaries over a 5 year period (how much was earned between two dates - start of year to end of year.  In the final year, the ending date was earlier than December 31.

I then created a couple columns in the data (for check purposes only, as I realize you cannot create these in your dataset!)  One for the fraction of the year, then dividing by the salary earned achieved the ANNUALIZED salary.

Ok, once satisfied I had something to solve for, I created the pivot table.  Then, I created 2 calculated fields (I assume you are familiar with these as your question uses this language, so I'll forgo what buttons to press to get there).

YearFraction = (end - start +1)/365 - not counting leap year as this is an example
AnnualizedSalary = Salary / YearFraction


Then, I put these in the pivot table and checked against the table I created in the spreadsheet and (presto) they were the same.

Here's hoping this is your solution :)

Please see attached demo example.  Please feel free to ask questions, if needed.


Enjoy!

Dave
PivotTableProration-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34958299
If this is the not direction you're going, or you want something more precisely like what you're trying to do, then please provide SAMPLE DATA to help with clarification.

Thanks for your indulgence as it will help speed responses and solutions!

dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34958313
If the solution doesn't suffice and you need help generating sample data or example - you could provide a print screen of your pivot table or even double click one of the value fields to generate a simple 1-row table that could be useful in assisting you further.

Cheers,

Dave
0
 

Author Comment

by:jaustinr
ID: 34960157
Dave,
I guess I should have used all 500 words (or a lot less) to describe my problem. :)  I really appreciate your taking the time to look at my issue.  You are exactly on the path of what I'm trying to do.  In your example, I have the year fraction.  Since I do an update 1-2x/month, so this is not an issue.  However, my "salary" variable is actually a "count" of salaries.  So, my real problem is how to do additional calculations (within PT) on the count.  At this point, my only solution may be to create the annualized data point back in Access where my original dataset resides.  However, not sure how that would interact with my PT and it makes the process a lot more complex.
Thanks again and any additional thoughts appreciated.

John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34973413
Just give me a few rows of made-up data, or a made-up snapshot of your pivot, and I can help, further.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34993718
Ok - willing to try this once more, without data - which would be really refreshing.  Many times we can give you exactly what you asked for, and yet that's not what you're LOOKING for.  A PICTURE is worth 1000 words and sometimes 1000 words don't get it :)

Here it goes.  I assume there's two people, with fraction thru the year called Annualized Fraction (which you say you have) and have received # of paychecks (no ryme or reason, that's just how many times they got paid).

So the # paychecks is your COUNT of salaries, for example purposes.

Then...

Annualized # paychecks (annualized count) = # Paychecks / Annualized Fraction.

If I'm off here, I'd like you to at least show an example to me just like how I'm explaining to you.

So if Mary has had 12 paychecks and her annualizing fraction = .5, the Annualized # Paychecks would be 24.

Here's the example, see attached range 36-48 of the data and pivot table.  I clicked in the data, then selected Pivot Table tools, then Formulas, and added a Calculated Field, called Annualized Count, with the formula above, see picture.
ExampleAlso, see attached.

Enjoy!

Dave
PivotTableProration-r2.xlsx
0
 

Author Comment

by:jaustinr
ID: 34994449
Dave,

Your most recent approach looks very promising.  I have to be out of town for a couple of days, but will definately try it when I return

Thanks for continuing to pursue

John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35065773
How's it going?  Is this working for you?

dave
0
 

Author Comment

by:jaustinr
ID: 35068328
Dave,

Just returned from travel and had a couple of things get in the way.  Plan on trying today or tomorrow.  Will let you know my results.

Thanks,

John
0
 

Accepted Solution

by:
jaustinr earned 0 total points
ID: 35218812
Dave,

Sorry for the delay.  The problem with your solution is that you have a count variable in your original dataset.  My count comes from using a variable in the data and using the Value Field Settings window to set the type calculation to Count (instead of sum, max min, etc.).  So when I use your approach to go to Tools/Formulas/Calculated Field, the only variable that show are the original variables, not the count of a variable (or any of the other standard variable calculations). I've played around using a "count(variable name)/annualize factor" in the formula field (for calculated value). This seemed to give me a proper value, but it creates additional problems. I have grouped years and it appears that the calculated value makes some changes in the PT cache.

Thanks for thinking about this and providing recommendations.  

John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35219902
I am willing to pursue this only if you provide sample dataset representing what you're working with.  

Cheers,

Dave
0
 

Author Closing Comment

by:jaustinr
ID: 35304183
Solution was found by expert guiding me through the logic process and I was able to find a direct solution.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question