[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

Dynamic Chart with data from continuous table

I'd like to create a dynamic chart which shows all orders by months. The user enters the start year and the end year. He or she can also select the orders by shop or all shops (value  0).  I use the following formula for counting the orders in C5:

=IF($A$2=0;SUMPRODUCT((YEAR(Orders[Date])=A5)*(MONTH(Orders[Date])=C5));SUMPRODUCT((YEAR(Orders[Date])=A5)*(MONTH(Orders[Date])=C5)*(Orders[Shop]=$A$2)))

Open in new window


The chart in my example workbook is static as I don't know how to make them dynamic. I need to use VBA to set the start year/end year and and fill the remaining rows with the correct year/month and the formula. I don't know how to do that. Everything is controlled with userforms and the chart will be copied in a userform. Can this also be done with a Pivot Chart? I'm not good with such charts though ..

Could you please have a quick look at my workbook and give me a clue how this can be done?

Thanks

Massimo

screengrabExample.xlsm
0
Massimo Scola
Asked:
Massimo Scola
  • 4
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you've made a good start with the Pivot Table. Group the date by month and year (right-click any one of the dates in the pivot table, then select "Group". In the next box, select "month" and "Year" and click OK).
Next, drag the Shop field into the Values box and set it to Count (not Sum).

Now create a Pivot Chart (click a cell in the Pivot Table, then click the Pivot Table Tools ribbon > Options > Pivot Chart.

Finally, click the Pivot table again and on the Pivot Options ribbon click "Insert Slicer".

Tick the boxes for "Years" and "Shop". Arrange the boxes and the chart.

Now you can select one (or more) years and one (or more) shops to be plotted in the chart.

See attached.

cheers, teylyn
27836151.xlsm
0
 
Massimo ScolaAuthor Commented:
hi thanks a lot
As the data in the table is set with userforms, I have to check how to set the values with VBA. I obviously need to know how to select all shops or only one .. the years etc.
I will change the values myself with the macro recorder running. That will give me a clue how to control a pivot table with VBA.
I will get back to this thread asap
massimo
0
 
Massimo ScolaAuthor Commented:
Hi

Well, I had a look at the code the macro recorder recorded and tried to amend it. PT are new to me, so my code doesn't quite work. For example: Why does .PivotItems.Count not count the correct number of items?

Option Explicit

Dim wbk As Workbook
Dim ws As Worksheet

Sub ShowShop(i As String)

'Set workbook variables
Set wbk = ThisWorkbook
Set ws = wbk.Worksheets("Pivot")

'PT Variable
Dim pt As PivotTable
Dim pi As PivotItem

'Set PT Name
Set pt = Tabelle3.PivotTables("PivotTable1")

'How many shops are in the field Shop?
countShops = pt.PivotFields("Shop").PivotItems.Count '<--- not correct, why?
    
'Speed Up
pt.ManualUpdate = True
    
    
    'If passed argument is 0, then display all shops
    If ShopID = 0 Then
    ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters

    Else
             
    'Otherwise deselect all items, and set the passed argument to be visible
    For Each pi In pt.PivotFields("Shop").PivotItems
    Select Case pi.Value + 0
        Case 1 To countShops
                    pi.Visible = False
                    With ws.PivotTables("PivotTable1").PivotFields("Shop")
                        .PivotItems(i).Visible = True
                    End With
        Case Else

        End Select
        Next pi
End If

pt.ManualUpdate = False
     
End Sub

Open in new window


The next step would be to add arguments for the years. How would you do that?
27836151.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Sorry, I don't do that kind of VBA,

But then again, you don't need VBA for that. Use Slicers instead of user forms. Users can select the slicers and the pivot table and chart will update.
0
 
Massimo ScolaAuthor Commented:
OK thanks for your help. You did help me. I know that I don't need VBA to do that but I have been asked to do it that way. :-( Maybe someone else can help me further..  I've updated my workbook. I now know how to count the items ..but don't know how to change the years. Here is my code, screengrab and workbook.

Option Explicit

Dim wbk As Workbook
Dim ws As Worksheet
Dim iCountShops As Integer


Sub ShowShop(i As String)
Application.ScreenUpdating = False

Set wbk = ThisWorkbook
Set ws = wbk.Worksheets("Pivot")

Dim pt As PivotTable
Dim pi As PivotItem

'PT name
Set pt = ws.PivotTables("PivotTable1")

'Count the number of shops
iCountShops = WorksheetFunction.Count(Range("Shops"))

'Speed Up
pt.ManualUpdate = True
pt.PivotCache.Refresh

'If 0, display all Shops.
If i = 0 Then
ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters

' Now I need to filter the years.
' Clear all filters
ws.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters
' Now I need to set the selected years - don't know how to.
' I assume that I need to loop through the years the same way
' I looped through all the shops.

                        
    'Otherwise display selected shop only. Remove all filters first.
    Else
    
    ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters
    For Each pi In pt.PivotFields("Shop").PivotItems

    Select Case pi.Value + 0
        'Set the visibility of each Item to False
        Case 1 To iCountShops
                    pi.Visible = False
                    'Set the selected shop to Visible
                    With ws.PivotTables("PivotTable1").PivotFields("Shop")
                        .PivotItems(i).Visible = True
                    End With
                    
                    ' Now I need to filter the years.
                    ' Clear all filters
                    ws.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters
                    ' Now I need to set the selected years - don't know how to.
                    ' I assume that I need to loop through the years the same way
                    ' I looped through all the shops.
                    
        Case Else

        End Select
        Next pi
End If

pt.ManualUpdate = False
Application.ScreenUpdating = True

End Sub

Open in new window


screengrabPivotVBA.xlsm
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Maybe the person who has asked you to do this in VBA, instead of using the out of the box tools that require just a few clicks, needs a little demo on what is now possible with Excel.

As Excel experts and providers of end user solutions we sometimes need to question the requirements and clauses. Very often requirements are based on wrong/old knowledge. They may not know that things are now a lot easier. They may ask for VBA because they cannot imagine that there may be something different.

Be bold. Why don't you show the person who has asked for a VBA solution how it can be done without VBA? Out of the box pivot and a few clicks adding slicers will be much cheaper and more easy to maintain than custom code.

But, if you prefer to re-invent the wheel, go for it!
0
 
Massimo ScolaAuthor Commented:
Hello Teylyn.

I demonstrated my boss the features and advantages of a Pivot Table and she was impressed. She decided to use Pivot Tables in worksheets instead of userforms.

Thanks for your help
Massimo
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now