Start Free Trial

asked on

Top 6 Reason Codes with Top 3 Stores using VBA

I had some previous assistance in getting the top 6 reason code and top 3 stores, however the code is not working fully and I have some additional criteria that was not provided at the time that I submitted the first request. In the attached workbook the first tab is a summary of the data tabs that follow it.

On the first chart (Schedule Delay) - I need to 1st provide the top 6 reason codes based on the pie chart and then the top 3 stores that have the highest number of days. This VBA is provided (Top6Top3 module) however it is not aligning under the first pie charts, the percentages are off, and the small box numbers are not pulling forward.

The second chart (Store Complete) - The data comes from the Complete tab and pivot table info is on PIVOTS tab. Criteria for Top 6 all reason code in the pie chart that are 5% and over and Reason Code is not 100-Comp. This is not a top 6 put such a list of the stores as we don't have any values to rank the stores.

The third chart (Cost Variance) - The data comes from the Budget tab and pivot table info is on the PIVOTS tab. Criteria for Top 6 all reason codes in the pie chart that are 5% and over and Reason Code is not ON-BUDGET. Top 3 is the stores with the highest variance %.

Please let me know if you have any additional questions that I have not addressed.
EE-Test-Scorecard-2014.01.09.xlsm

Hi
Are the labels filled with the correct data under each chart ?
gowflow

ASKER

Hi Gowflow,

Here is an revised workbook, there some minor updates where the data changed.
EE-Test-Scorecard-2014.01.09.xlsm

I appreciate you attached the latest workbook that is fine. But still I do not appreciate when my questions are not answered ! This build frustration. I ask the question again:

Are the labels filled with the correct data under each chart ?

Gowflow

ASKER

Sorry for the miscommunication in answering your questions, based on the last workbook that I uploaded the labels match what is under each chart.

ok fine. Let me work on all this, as I see from your initial post that the source for each chart is not the same hence we cannot adopt a similar approach of calculation for each chart. Each one is an own entity and need its own variables.

Am I right ? If not pls correct my understanding.

I would like to please summarise in 3 lines what are the input sheet and data tables for each pie.

Like pie1 (the left most) picks data from sheet X and from table Y ...
pie2 center etc...
pie3 right most etc ...

Rgds/gowflow

ASKER

Correct each one has it own variables

Pie 1(left) - picks data from sheet - RCSupport and OnTimeCals; pivot table from RCSupport
Pie 2 (center) - picks data from sheet - Complete; pivot table from PIVOTS
Pie 3 (right) - picks data from sheet - Budget; pivot table from PIVOTS

Let me know if you have any additional questions. Thanks

Yes the last post is GREAT !!! this is the type of info to the point that I need.

You mentioned somewhere in your question post that pie 2 has no top6 and stores or something like that ?? but aren't we getting top6 and top3 for all the graphs ?

If there is exceptions pls clearly state for each pie what you expect in the labels. short and sweet.

gowflow

ASKER

Pie Chart 1 - Top 6, Top 3
Pie Chart 2 - Top 6, List of store names
Pie Chart 3 - Top 6, Top 3

ok fine let me work on all this but you will need to be a bit patient but hopefully will pull this thru !!! :)
Regards/gowflow

ok here it is:

I hope we can have a quick and final exchange. Just a small note, unfortunately the past time you had raised this question of top6 and top3 I had posted a reply but it seemed you used an old version reason why you had wrong figures. Anyway I worked on all 3 graphs as per your explanation I will ask you to go thru the figures and check each one.

The charts are:
Chart1 left most
Chart2 Center
Chart3 Rightmost

I would suggest we go Chart by Chart.

Do the following download this version and what I did is I pushed further down your data labels so you can have them as referential. Activate the Macro Top6Top3 and see the results.

Take figures for Chart1 and check all of them and if you see discrepancy with your figures then check in the table to make sure either you were wrong or if I am wrong then you tell me what it should do and what I missed.

After you finish with Chart1 please post the reply so will work chart by chart.

Regards
gowflow
EE-Test-Scorecard-2014.01.15.xlsm

ASKER

Thanks, will have feedback for you tomorrow.

ok fine no problem
gowflow

ASKER

So I tested chart 1 in the workbook that I downloaded from you. Top 3 did not generate correctly, please see the attached.
EE-Test-Scorecard-2014.01.15-cha.xlsm

Where are your comments as per my request ?? I could see it is not correct right from the beginning I need from you how you get to your figures in details !!! like what divide by what multiply by what etc....

ARE WE BACK TO SQUARE ONE in no understanding !!!!
gowflow

try this file
gowflow
EE-Test-Scorecard-2014.01.18.xlsm

ASKER

Hi,

Tested the first chart again. The top 6 info is correct and I validated by getting the % from the pivot table on the RC Support tab.

The top 3 is bring back the incorrect data, only the first box has the correct top 3 however the number of days is incorrect. Validated by creating a pivot table with the data on the OnTimeCals tab. The manual check can be found on the OnTimeTables.

Please let me know if you need any additional info.
EE-Test-Scorecard-2014.01.18-Res.xlsm

Ok here are my findings:

1) I noticed that the problem we have is that when the macro reaches the point that it need to sort the sheet OnTimeCals for each Top6 to find the top3 stores the sheet does not sort properly. It is maybe due to mixture of formulas that makes the sorting goes wrong.

2) SO to try to overcome this and to troubleshoot, I made a copy of this sheet into a new sheet called OnTimeCals2 and pasted the values so we do not have formulas.

3) I modified the macro for graph1 to look for OnTimeCals2 instead of OnTimeCals and here are the results in the attached file.

4) I noticed that the values for the store top3 comes out correct however the description is wrong. SO my question to you how are your pivot tables made what is the Column that you look for for store name isn't is Col B labeled Store Name in sheet OnTimeCals ??? If yes then I am afraid that your pivot also are giving you the wrong info !!!

So please check the data in this chart and for me it seems it is correct. But if you see a different way of doing please explain it to me.

Rgds/gowflow
EE-Test-Scorecard-2014.01.22.xlsm

ASKER

Hi gowflow,

1. Do you have a recommendation on how I can work around the formulas in the OnTimeCals tab since they are proving to be an issue? May be copy the data from OnTimeCals to OnTimeCals2 with VBA?

2. I refreshed the pivot table and came up with the same results when the macro is run.

YES I can do the copy in VBA that is no sweat, but first I need you to confirm are my results via OnTimeCals2 Correct ??? Like you had previous wrong results and we found something new ?? I am not clear on what is right from wrong.

What do you mean by:

2. I refreshed the pivot table and came up with the same results when the macro is run.

gowflow

ASKER

Yes, the results that you have in the OnTimeCals2 tab are correct. Verified that I received the same results in the tables on the OnTimeTable tabs using the data from the OnTimeCals tab.

Yes but then why the store names are wrong ??? they do not match the data that are in the black labels that you created this is where I am confused.
gowflow

ASKER

When I was troubleshooting I noticed that two of the calculations was looking at the wrong line so everything got shifted by a couple of lines so it shifted numbers to the wrong store.

and ... bottom line ? Mine are correct ? all correct ?
gowflow

ASKER

Bottom line all of yours is correct

ok fine.

Here is my proposal:

AS this is getting way too complex, Below is my solution for Graph1. Please test it and if you like it I suggest you close this question and open a related one so we can move to Graph2 and please in the draft of the next question pls specify your request for the second graph as to the sheets to use and if top6, top3 ? this small details that would help us minimize the wasted time.

Regards/gowflow
EE-Test-Scorecard-2014.01.24.xlsm

Did you have a chance to test it ? Please let me know on how you would like to proceed.
gowflow

ASKER

Looking over, if okay will close this request and open a new one for chart #2

great !!! pls don't forget to put is short and sweet in the question with a link in here so I can be notified.

I hope we are now on the right track ... after looong working on demystifying all this !
:)

gowflow

Any news on this issue ? No new question posted ? this one still open ! Do you have any problem with this ? Did I miss your post ?
Let me know

gowflow

ASKER

Hi gowflow,

I tested in the sample file that we have been working with and everything looks good. Do you have any recommendation on how I can troubleshot my live file it is erroring on the

 '---> Sort the Table by #Days Reverse
    TblAddress.Sort key1:=WSPivot.Range(StTbl).Offset(0, 2), order1:=xlDescending, Header:=xlGuess

Open in new window

?

Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Retested and it is now working 100% in the live version of the report, not sure what was going on last week.

ASKER

Here is the link to the new request: https://www.experts-exchange.com/questions/28360989/Part-2-Chart-2-Top-6-and-List-of-Stores.html