Solved

Part 3 - Chart 3 Top 6 Top 3

Posted on 2014-02-21
11
168 Views
Last Modified: 2014-03-07
Chart 3 -  Top 6, Top 3 (similar to Chart 1)

Pie 3 (right) - picks data from sheet - Budget; COST pivot table from PIVOTS.  
1. The Table COST should at all time be filtered by the Status OPN  and then perform calculations.
2. When creating the Top 6 Reason Codes I would like to have the reason code ON-BDGT excluded from the results.
3. Top 3 will be the stores with the highest variances found in Column M of the Budget worksheet.  
4. Would like the results to look as follows:
      Partner- Site, 13s                 25%
      Mila-57115 - 57%
      Jem-57675 - 54%
      Stoc-56225 - 44%
EE-Test-Scorecard-2014.02.21-Cha.xlsm
0
Comment
Question by:jmac001
[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
  • 8
  • 3
11 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39878866
I would say it is more a combination of Chart1 and Chart2 as it have all the layout and filtering of Chart2 but only the top3 by value of Chart1.

... working on it
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39879117
2 things:

1) I noticed in PIVOTS COST you have beside ON-BDGT that we need to remove you also have (blank) Store shall we remove this one as well ?

2) If we remove these 2 should we do it from the PIVOT table like filter the pivot without these 2 items ? this means that
instead of having what we have now
ON-BDGT      34
PAR-SITE              13
(blank)                2
SDC-DESN        2
BRD-AGMT        1
Grand Total      52

We should instead have:
PAR-SITE              13
SDC-DESN        2
BRD-AGMT        1
Grand Total      16

Is that Correct the total is now 16 and not 52  ???

3) Then we are looking for Top6 but here we have only 3 so we should go to minimum what we have items not to exceed 6 Is that logic correct ???

As we are not using this logic anywhere else.
gowflow
0
 

Author Comment

by:jmac001
ID: 39882465
Hi gowflow

1. Yes I would like to have the blanks excluded from the top 6 results, however I would like them to remain in the pie chart.

2. The correct total would be 16

3.  Yes the logic would be not to minimum of what is in the data and not to exceed 6 reason code
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 30

Expert Comment

by:gowflow
ID: 39882525
all is ok except you said:
3.  Yes the logic would be not to minimum of what is in the data ....

I guess you meant:
3.  Yes the logic would be minimum of what is in the data and not to exceed 6 reason code

Pls confirm
gowflow
0
 

Author Comment

by:jmac001
ID: 39882591
Yes, I apologize I did mean the logic would be minimum of what is in the data.
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 39885045
ok I hope this is what your looking for. This version has following ammendments:

1) I fixed Chart3 based on above settings.

2) I fixed Chart1 to loop also thru the same limitations (minimum what is there to maximum 6)

3) I fixed the layout as to number of days and percentage so it looks esthetically the same and ok. You will have always the percentage on the right on first row at the heading and then on the left second line corner the number of days. As you can see every label is different and due to different description width it is difficult to align data.

Check it and let me know your comments.

NB this version will display all 3 charts.
gowflow
EE-Test-Scorecard-2014.02.22.xlsm
0
 

Author Comment

by:jmac001
ID: 39887289
Hi gowflow,

Testing will update and let you know if I identify any issues.

Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39888177
ok fine let me know.
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39898416
Any news on your trials ?
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39909393
I take it that the solution worked for you as you did not comment. Pls advise if you need further help on any other issue by posting a link in here.

Regards/gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39913003
Hi jmac001

I usually keep the last question opened or monitored for people who I worked extensively on certain items which happen to be your case.

Would you need other help in the future or its okay if I close this question ?
gowflow
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 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