• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic pivot and chart without VBA

L

Lolo

Member
Hello,

I am currently creating a dynamic dashboard.
I want to be able to see on a pivot table and on a chart, data visualized either by week, either by month.

I have done this with VBA it works fine, see the file in attached.
For this I dynamically modfy a table data (in sheet CALC_CHART) based on the pivot table, then the chart use the data of this table. Indeed, in my real dashboard, there is several data columns and several charts, so use a basic chart linked to the pivot table, is not a good solution for me, because, the chart would display ALL the data columns of the pivot table in this case. And I want to show only some data column, depending of charts concerned. With this dynamic table I can use only one source, for several charts.
The file in attached is simplfied with only one data column. So don't tell me to use a chart based on the pivot table in this case :)

I'm wondering if there is a way to do the same kind of "dashboard' WITHOUT VBA ? Perhaps/probably I use VBA while it is not necessary.
* How could I dynamically change the pivot field (week or month) of the pivot table ?
* How could I dynamically update the chart too accordingly ?

Thank you for your ideas and experience :)
 

Attachments

  • TEST DYNAMIC.xlsm
    142.4 KB · Views: 26
Hi Lolo - It is possible without VBA...I am working on it and will share a sample in a while...
 
Hi Lolo - Please see the attached...

Let me know if you are looking for something else..
 

Attachments

  • Book1.xlsm
    14.8 KB · Views: 68
Thank you, very clear, and it was this kind of solution I had in mind.
What I missed previously, when I tried to do it myself, was the RngTotal formula, in order to dynamically define the range for the chart.

However, I have another constraint. Another reason I used pivot tables is the amount of data. Data sheet has 140 000 lines actually (1 year of data). With my solution, it works fine, because I use the power of pivot tables to consolidate data by month or week. So I have to check if formulas (SOMME.SI.ENS) works fine in term of performance, in case of big amount of data (and in the real dashboard, I have 10 columns, not one, so means 10 columns of formulas)...

Depending on the amount of data, we could use one solution or another...
 
Lolo, note the formula to get the unique is an array formula...using this array formula on the kind of data that set you have may result in latency..In such cases, I either switch to helper columns for non array solution or I rely on VBA to help me out...

Why don't you try once using the formula that I have given and if you really face any issues then we can work another solution out..
 
Hello sorry for my late answer...

I have tested with a lot of data and formula only, and as expected it is long, more than using Pivot table + VBA. But this is OK for me, using VBA and pivot tables allows me to make a lot of things, that would be perhaps more tricky or impossible with just formulas. So my solution is OK for me. However thank you for your help, by showing me a way to use formula only ;)
 
You must log in or register to reply here.
Back
Top