• 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.

Chart without date with no date

Pierre

Member
Hello,
I'm working on a chart where I view performance between 2 dates that I enter. The only thing is when I have a weekend or dates with no data, I see it anyway in my chart. I tried to use the "text" formatting for my axis, but then I need my data array to be dynamic and as I use a FILTER formula, that doesn't seem to work...

Anyone with an advice on how to do?
 

Attachments

  • Productivity_Dashboard_dynamic chart.xlsm
    511 KB · Views: 13
Hello,
I'm working on a chart where I view performance between 2 dates that I enter. The only thing is when I have a weekend or dates with no data, I see it anyway in my chart. I tried to use the "text" formatting for my axis, but then I need my data array to be dynamic and as I use a FILTER formula, that doesn't seem to work...

Anyone with an advice on how to do?
Hi, Pierre,
didn't test it (as I cannot use Excel365 / FILTER function, I use MS classic Excel), but I know the data is self-hiding in the charts if you simply hide your week-end data.
Cheers,
Romeo
 
Pierre
1) for cell R2 ="" & text(N2,"dd-mmm-yy") (( or as You like to see those dates ))
2) next use R-columns 'dates' for 'Horizontal (category) axis labels'
OR
someway use above formula with Your N-column ... formula (which won't work with me).
Screenshot 2021-03-13 at 22.40.14.png
Above would work if You would use some basic Excel features which would work with many Excel-version.
 
I'm just wondering whether you wouldn't find it an awful lot simpler to use a pivot table and chart.
In the attached, on the sheet Graphe_Table, from column W and to the right, I've added a pivot table, a chart and a timeline which seems to do most, if not all that you need.
I've added one column (M) to the Returns core table, being the Cible UPH.
No macro's needed except for one short one to restore the chart's settings when they're lost when selecting dates outside your range of dates.
The timeline is simple to operate; you can click and drag, use the Shift key to help select dates as well as chosing Days/Months/Quarters/Years in the top right of the timeline, as well as showing all dates by clicking the icon in the very top right of the timeline.. Choose your employee either in the chart (top left) or the pivot table (cell X1).

I'm not sure whether this will address you seeing/not seeing dates, but I feel sure that we should be able to get the chart to show/not show just what you want.

(I've disabled the Worksheet_Selection_Change event handler because it couldn't find the EmployeCombo.)
73835
 

Attachments

  • Chandoo45802Productivity_Dashboard_dynamic chart.xlsm
    669.4 KB · Views: 0
Last edited:
Back
Top