Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pivot Chart best possible

Posted on 2012-03-22
9
Medium Priority
?
315 Views
Last Modified: 2012-03-23
I need to do a pivot chart.
SampleP.xlsx
0
Comment
Question by:Rayne
[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
  • 7
  • 2
9 Comments
 

Author Comment

by:Rayne
ID: 37755158
Each division of a company has a $$ liabilities that it had to clear against its budget that it gets from the mother company. So, if Div 1 gets a quarterly budget of support from the mother company in the amount of $22000 and if it’s current liabilities towards the mother company, on that specific date in $2000, then actually the budget it will get will be $20000.

Now, the idea here is dollar changes in liability over a period of time as divisions are constantly either paying off the liabilities or accruing them over a period of time... What could be the best representation in terms of pivot chart that could visually and metrically show the user that info? Like if we see that out of all divisions, any particular division has increased liabilities over a period of time or sudden hike in liabilities over a long period of time. Any multitude of interesting trends that will give deeper insights into how the different divisions are maintaining their liabilities over a long period of time or any interesting trends in the chart..? So what could be the best pivot chart to represent this visually? I have already attached an excel pivot chart. But I think it could be much better in shape, visually and more intuitive. Please Help.

Thanks
Rayne
0
 

Author Comment

by:Rayne
ID: 37755968
Any ideas ?
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37755975
I took a look at this a few hours ago and thought you had a pretty good chart.

You want to express to the user that the divisions are paying off liabilities or accruing them over a period of time, correct?

I think one way to think about it is the percent change in liabilities and show that as a line on the secondary axis.

Do you have access to the budget?  liabilities/budget might be another measure which is readily trend-able.

Dave
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 42

Expert Comment

by:dlmille
ID: 37755992
At an executive level, the less metrics the better, and the more meaningful the critical few measures that are observed, the better (at least that's been the trend from my perspective over the last 10 years - simpler/less is better but it has to be good!).  With that in mind, there are two metrics and potentially one (re: risk) that might serve as the measure you're looking for.


Here's an example adding two additional lines, one for average liabilities over the period, and the other for standard deviation (so a measure of how uncertain the holding company might be about the division's liability management - a measure of risk, perhaps).  I sorted the divisions in the order ascending based on standard deviation/risk perceived.

See attached.

Dave
SampleP-r1.xlsx
0
 

Author Comment

by:Rayne
ID: 37757877
Hi Dave,

Thank you so much  for your kind help and observation The divisions are small and several, so with time, I guess the x axis will get longer.
That excel sheet will grow bigger with more row data added for liabilities added over a several months and weeks.
The measure of variability and average is great.

Further, the other thing is is there a way to show, that for Each division, at any specific time - the liabilities have fallen or increased by % like adding a third trend line (in addition to the two existing ones already)?
more like the photo....
0
 

Author Comment

by:Rayne
ID: 37757883
0
 

Author Comment

by:Rayne
ID: 37758177
You want to express to the user that the divisions are paying off liabilities or accruing them over a period of time, correct?
Yes



I think one way to think about it is the percent change in liabilities and show that as a line on the secondary axis.
Yes, that a very good idea



Do you have access to the budget?  liabilities/budget might be another measure which is readily trend-able.
No. I don’t have access to budget info
0
 

Author Comment

by:Rayne
ID: 37758723
Thanks Dave for your kind help.
0
 

Author Closing Comment

by:Rayne
ID: 37758726
Very good expert
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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