Solved

Part 2 - Chart 2 Top 6 and List of Stores

Posted on 2014-02-10
20
181 Views
Last Modified: 2014-02-21
Hi,

Tested Chart 2 and the Reason Code Name, # days, and % are all the same.  The labeling for the Top6 is the reason code name, #s, %. #s represents the number of stores.  

Chart 2 is the top 6 reason codes from the pie chart Stores Complete at Open. For each of the Top 6 reason codes show the store name with the reason code in the data.   Pivot table data located on the PIVOTS tab, the data is found on the Complete tab.

Please let me know if any additional information is needed.
EE-Test-Scorecard-2014.01.24-tes.xlsm
0
Comment
Question by:jmac001
  • 10
  • 7
  • 3
20 Comments
 

Expert Comment

by:DILipandey
ID: 39849262
Hi jmac001,

Is this a query from your side or a kind of knowledge sharing?  Thanks.



Regards,
DILIPandey
0
 

Author Comment

by:jmac001
ID: 39850025
It is a query, I've been working with gowflow on getting the information to populate using VBA.
0
 

Expert Comment

by:DILipandey
ID: 39850036
Okay.. what is your query - can you explain it in detail?  Thanks


Regards,
DILIPandey
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39854077
ok pls lets recap what is needed for Chart2

1) top 6 same as Chart1 ?
2) Top3 stores same as Chart1 ? if yes state it. if no pls advise what is needed then referring to an example on a table and sheet. as cannot relate the data that you manually inputed in black cannot find the source.

gowflow
0
 

Author Comment

by:jmac001
ID: 39860004
Chart 2 is:
1.) Top 6 same as Chart 1
2.) Just a list of the stores by name for each Top 6
3,) Data Source is on the Complete Tab
4. The pivot table is on the PIVOTS tab
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39861081
Well you are a pro in delays and in lack of information.

I am looking close at Chart2 and I see major diffrences with Chart1

1) You said to use table PIVOTS (so I presume it is the table that has a title COMPLETE)
2) You said to use the Data table Complete. This table has not the same format as the previous OntimeCals in Chart1 which had the store names on Row1 this one has no store name in Row1 whatsoever it has in Col B the Store Name
3) If you look at the PIIVOT sheet pivot table COMPLETE
0                      41
100-COMP      31
MST-VNSHP      10
PAR-OSM      9
PAR-CONT      5
MST-EXTLC.      1
MST-TOFF      1
SDC-DESN      1
Grand Total   99

what is the item 0 ? then item 100-COMP does not exist in sheet Complete also store MST-VNSHP does not exist in Sheet Complete

Can you please explain what do we need to extract and how with an example.

The faster you reply the faster you will get an answer and the faster the Expert working on your issue can have all the details fresh. As when time passes it takes more time to remember the whole logic and then you have less chances to get an accurate response.

Regards
gowflow
0
 

Expert Comment

by:DILipandey
ID: 39862358
I completely agree with GowFlow.


Regards,
DILIPandey
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39863190
Any news jmac001 ?
Try to be quick this time !!!

Regards
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39866961
jmac001

It seems you are either busy or ... ? can't explain these delays in replying. Sorry to push but if your interested in a solution you need to improve the speed of replies.

Regards
gowflow
0
 

Author Comment

by:jmac001
ID: 39867664
1. Yes the table listed Complete is the correct Pivot on the PIVOT Tab
2. You are correct the formatting is not the same as the OnTime Cals tab, however the store name is in the same column (B) in each of the tabs
3.  The filter for Status is looking at all records and not just those that are OPN, so those the 0 - 41 would be stores with no reason codes populate.   100-COMP and MST VNSHP are both on the Complete tab in and appear in Columns J (Reason Code) and K (Reason Code 2).  

The information that needs to be extracted is the information from Pie Chart 2 - Store Complete at Open for the Top 6 reason codes by percentage excluding 100-COMP.  

The pie chart is populated from the Complete pivot table on the PIVOT table using the data from the Complete tab specifcially the Reason Code 2 field.  

Based on the spreadsheet attached the first reason code would be :

Mast-Vendor/Shopfitter, 10s  17%
PRAG-55298
IBN -52957
HAMB-58035
GURN-57655
SABI-57517
STOC-56225
ATAT-55297
DALA-57117
MOSC-56213
ATHE-56182
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Expert Comment

by:gowflow
ID: 39869922
Ok thank you for replying, I have these question:

 I see the logic now is totally different and you introduce Reason code that were not there before. Now with your explanation I can see some light at the end of the tunnel however I still have questions:

1) Shall we ALWAYS ignore in the PIVOT (for this Chart obviously) items that are
0
100-COMP
?? As you mentioned that the first item would be MST-VNSHP so I assumed and you explained it that we need to ignore the 0 and 100-COMP
is that correct ??

2) What is the 0 -41 that you refer to in your post 3. ??? I need to know what are the items that you do not want to take in consideration in all cases not specifically now in this example as the routine could work now and fail later. My feeling is that you should probably have a certain criteria in the Complete table that you filter by to exclude certain items and this is the criteria I need. Remember I need to replicate exactly how you do it manually, so we come up with the same results.

3) I assume then that we need to SORT the table complete by the Col K that is Reason Code 2  will this be ALWAYS the case ? Like always will sort by Col K???

Please answer as fast as possible to keep the momentum going as the whole thing now is fresh. It took me long to be able to dissect all this and remember we do not know your business you know it so if you don't provide the smaller details you will end up not getting a solution.

Regards
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39870309
Having not answered, I assumed the following:

1) Yes
2) No idea still need to know.
3) Yes

Pls try this version and let me know if it give the expected results for Chart2. This is the Code for Chart2

Sub Chart2(WSChart As Worksheet, ChrtIndex As Integer)
Dim WSPivot As Worksheet
Dim WSData As Worksheet
Dim WSDataTemp As Worksheet
Dim WSDesc As Worksheet
Dim Tbl As PivotTable
Dim TblAddress As Range
Dim StTbl As String, EndTbl As String, LeftLabel As Integer, TopLabel As Integer, lenBold As Integer
Dim Code As String, CodeDesc As String, ColCode As Integer, AvgDays As Integer, PercOpen As Double
Dim I As Long
Dim cCell As Range
Dim Sh As Shape
Dim Group(12)

Set WSPivot = Sheets("Pivots")
Set WSDesc = Sheets("RCSupport")
Set WSDataTemp = Sheets("Complete")

'---> Create New WS copy of Data pasted as values
On Error Resume Next
Set WSData = Sheets("Temp")
If Err <> 0 Then
    '---> Sheet Does Not Exist, Create it
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Set WSData = Worksheets(Worksheets.Count)
    WSData.Name = "Temp"
End If
On Error GoTo 0
WSData.Cells.Delete
WSDataTemp.UsedRange.Copy
WSData.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats


'---> Get Pivot in WSPivot
For Each Tbl In WSPivot.PivotTables
    If WSPivot.Cells(1, Tbl.TableRange1.Column) = "COMPLETE" Then
        Tbl.ClearAllFilters
        Set TblAddress = WSPivot.Range(Tbl.TableRange1.Address)
        StTbl = WSPivot.Cells(TblAddress.Row + 1, TblAddress.Column).Address
        EndTbl = WSPivot.Cells(TblAddress.Rows.Count + 2, TblAddress.Column + TblAddress.Columns.Count - 1).Address
        Set TblAddress = WSPivot.Range(StTbl & ":" & EndTbl)
    
        '---> Sort the Table by #Days Reverse
        TblAddress.Sort key1:=WSPivot.Range(StTbl).Offset(0, 1), order1:=xlDescending, Header:=xlGuess
        
        '---> Remove Undesirables codes from the table
        Set TblAddress = Nothing
        For I = WSPivot.Range(StTbl).Row To WSPivot.Range(EndTbl).Row
            If WSPivot.Cells(I, WSPivot.Range(StTbl).Column) <> "0" And WSPivot.Cells(I, WSPivot.Range(StTbl).Column) <> "100-COMP" Then
                If TblAddress Is Nothing Then
                    Set TblAddress = WSPivot.Range(WSPivot.Cells(I, WSPivot.Range(StTbl).Column), WSPivot.Cells(I, WSPivot.Range(StTbl).Column + 1))
                Else
                    Set TblAddress = Union(TblAddress, WSPivot.Range(WSPivot.Cells(I, WSPivot.Range(StTbl).Column), WSPivot.Cells(I, WSPivot.Range(StTbl).Column + 1)))
                End If
            End If
        Next I
        Exit For
    End If

Next Tbl

'---> Get the Chart
'For I = 1 To WSChart.ChartObjects.Count
With WSChart.ChartObjects(ChrtIndex)
    LeftLabel = .Left + 3
    TopLabel = .Top + .Height + 15
    WidthLabel = .Width / 3 - 5
End With


'---> Get the Top6
For J = 1 To 6
    With WSChart.Shapes.AddTextbox(msoTextOrientationHorizontal, LeftLabel, TopLabel, WidthLabel, 100)
        '---> Set Font type and size
        .TextFrame.Characters.Font.Name = "Calibri"
        .TextFrame.Characters.Font.Bold = True
        .TextFrame.Characters.Font.Size = 8
        
        '---> Add Rectangle in the Bottom Right Corner with the Ranking
        With WSChart.Shapes.AddShape(msoShapeRectangle, LeftLabel + WidthLabel - 20, TopLabel + 80, 20, 20)
            .TextFrame.Characters.Text = J
            .TextFrame.HorizontalAlignment = xlHAlignCenter
            .Line.DashStyle = msoLineSolid
            .Line.Style = msoLineSingle
            .Line.ForeColor.RGB = RGB(0, 0, 0)
            .Name = "Cht " & Format(ChrtIndex) & " Rec " & Format(J)
            Group(J) = .Name
        End With
        
        '---> Loopukup Code Name, Avg days, %Open Days
        Code = TblAddress.Cells(J, 1)
        AvgDays = TblAddress.Cells(J, 2)
        Set cCell = WSPivot.Columns(TblAddress.Column).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            TotRow = cCell.Offset(0, 1)
        Else
            TotRow = TblAddress.Cells(TblAddress.Rows.Count, 3)
        End If
        
        PercOpen = TblAddress.Cells(J, 2) / cCell.Offset(0, 1)
        
        Set cCell = WSDesc.Range("A:A").Find(what:=Code, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            CodeDesc = WSDesc.Cells(cCell.Row, 2) & ", "
        Else
            CodeDesc = "Item not found, "
        End If
        
        '---> Add Text of the Header
        .TextFrame.Characters.Text = CodeDesc & AvgDays & " d" & Space(25)
        .TextFrame.Characters.Text = Left(.TextFrame.Characters.Text, 30) & Chr(9) & Format(PercOpen, " 0%") & Chr(10)
        
                
        '---> Find Col of the Code in the Table DataII
        Set cCell = WSData.Range("1:1").Find(what:="Reason Code 2", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            ColCode = cCell.Column
        Else
            ColCode = 1
        End If
        
        '---> Make sure to remove filtering before sorting
        If WSData.AutoFilterMode = True Then WSData.ShowAllData
        
        '---> Sort the Table by  Ascending for that Code
        WSData.Range("A1").Sort key1:=WSData.Columns(ColCode), order1:=xlAscending, Header:=xlYes
        
        '---> Filter the Table for that Code
        WSData.UsedRange.AutoFilter Field:=ColCode, Criteria1:=Code
        
        '---> Record Length till now
        lenBold = Len(.TextFrame.Characters.Text)
        
        '---> Add All Stores
        For K = 2 To WSData.Range("A" & WSData.Rows.Count).End(xlUp).Row
            If WSData.Cells(K, 1).EntireRow.Hidden = False Then
                If .TextFrame.Characters.Text <> "" And Len(.TextFrame.Characters.Text) <> lenBold Then .TextFrame.Characters.Text = .TextFrame.Characters.Text & ", "
                .TextFrame.Characters.Text = .TextFrame.Characters.Text & WSData.Cells(K, 2)  '& "  -  " & WSData.Cells(K + 1, ColCode) & " days" & Chr(10)
            End If
        Next K
        
        '---> Set Top 3 as normal not bold
        .TextFrame.Characters(lenBold, Len(.TextFrame.Characters.Text) - lenBold).Font.Bold = False
        
        '---> Name the TextBox
        .Name = "Cht " & Format(ChrtIndex) & "Top " & Format(J)
        Group(J + 6) = .Name
        
        '---> Format the Box
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        
        
        '---> Set Position of the Next Box
        If J <> 3 Then
            LeftLabel = .Left + .Width + 3
        End If
        
        If J = 3 Then
            LeftLabel = WSChart.ChartObjects(ChrtIndex).Left + 3
            TopLabel = TopLabel + .Height + 3
        End If
        
        
    End With

Next J

'---> Group All the Shapes

WSChart.Shapes.Range(Array(Group(1), Group(2), Group(3), Group(4), Group(5), Group(6), Group(7), Group(8), Group(9), Group(10), Group(11), Group(12))).Group
'WSChart.Shapes.Range(Array("Top 1", "Rec 1", "Top 2", "Rec 2", "Top 3", "Rec 3", "Top 4", "Rec 4", "Top 5", "Rec 5", "Top 6", "Rec 6")).Group'
'WSChart.Shapes.Range(Array("Top 7", "Rec 7", "Top 8", "Rec 8", "Top 9", "Rec 9", "Top 10", "Rec 10", "Top 11", "Rec 11", "Top 12", "Rec 12")).Group
End Sub

Open in new window


Activate Macro CreateTop6Top3Labels

gowflow
EE-Test-Scorecard-2014.02.19.xlsm
0
 

Author Comment

by:jmac001
ID: 39871114
Goflow,

Response to questions:

1.   For building the top 6 you would exclude reason codes 0 and 100-Comp. Reason code 0 means the store does not meet the critera of status = OPN.  100-Comp shows the store opened Complete and I only what to identify top 6 reason the store did not open complete.

2. If the reason code 2 field is blank and the status is not OPN then it is being excluded from the PIVOT and CHART2 and should also be exluded from being calculated in the Top 6

3. Yes if it necessary to sort the data to get the information to populate then it would be ColumnK-Reason Code 2

I will continue to test the workbook that you have attached and let you know if I have any problems.
0
 

Author Comment

by:jmac001
ID: 39871182
Hi Gowflow,

The only fix is related to the your questions regarding the 0 reasons code.  Once this is excluded from the being populated in the pivot and chart then it looks like every thing will populate correctly.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39871552
I do not understand your last post. Is there a problem with my solution ?
gowflow
0
 

Author Comment

by:jmac001
ID: 39871750
The VBA changes the Complete Pivot table results to look at all records.  The criteria for the  table is Status = OPN only and not ALL.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39872440
You mean to say that the Table Complete should at all time be filtered by the Status OPN ? and then perform the rest of calculations?

Or you need at the end of the calculations to get back the filtering to be OPN for sheet Complete ?

Like I am not clear on where the problem is.
Are the charts lables come out correctly but you need OPN on complete ?
or
Chart Labels wrong because OPN is not on ?
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39872685
I think after reading several times in between the lines .. I finally got what you meant to say without explicitly saying it and here it is:

You need the Table in the PIVOTS sheet to ALWAYS be filtered by OPN. My routine set the filters to all prior to starting which makes the total 99 instead of 56 when it is set to OPN. Although the stores comes out ok the total percentage comes out wrong.

Please check this version and I hope this is what you are looking for.

I please invite you to be more explicit and choose your words and read your post prior to posting as it would save ALL OF US a HELL OF A TIME trying to decrypt your wishes and intentions.

AS and Example read your question again putting yourself in our shoes like we know nothing of your business I will quote just 2 lines and you will see it is TOTAL CHINEESE with all due respect to chineese I know nothing of that language:


Tested Chart 2 and the Reason Code Name, # days, and % are all the same.  The labeling for the Top6 is the reason code name, #s, %. #s represents the number of stores.  

READ this sentence and I guarantee NO OONE on earth could understand what are reason code, #s, % #s .... without prior correct reference to these items explaining where they come from or what they are !!!!

REGARDS
gowflow
EE-Test-Scorecard-2014.02.20.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39876063
Tks and glad we finally got to this one.
PLs post a link in here for any other question you may need help with. Presume you still have the issue of Chart3 which I hope the explanation will be smooth.

Regards
gowflow
0
 

Author Comment

by:jmac001
ID: 39878116
Here is the link to Chart 3 Request. Hope that it is easy to understand the request.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28371275.html
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now