Solved

# Dynamic Chart with data from continuous table

Posted on 2012-08-21
607 Views
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)))
``````

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

Example.xlsm
0
Question by:mscola

LVL 50

Accepted Solution

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

Author Comment

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

Author Comment

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
``````

The next step would be to add arguments for the years. How would you do that?
27836151.xlsm
0

LVL 50

Expert Comment

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

Author Comment

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
``````

PivotVBA.xlsm
0

LVL 50

Expert Comment

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

Author Closing Comment

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.

Massimo
0

## Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…