Part 2 - Chart 2 Top 6 and List of Stores

Hi jmac001,

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



Regards,
DILIPandey

It is a query, I've been working with gowflow on getting the information to populate using VBA.

Okay.. what is your query - can you explain it in detail?  Thanks


Regards,
DILIPandey

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

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

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

I completely agree with GowFlow.


Regards,
DILIPandey

Any news jmac001 ?
Try to be quick this time !!!

Regards
gowflow

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

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

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

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

Select allOpen in new window

Activate Macro CreateTop6Top3Labels

gowflow
EE-Test-Scorecard-2014.02.19.xlsm

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.

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.

I do not understand your last post. Is there a problem with my solution ?
gowflow

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.

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

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

Here is the link to Chart 3 Request. Hope that it is easy to understand the request.

https://www.experts-exchange.com/questions/28371275/Part-3-Chart-3-Top-6-Top-3.html