Here’s an issue that can be rather annoying. You want to display activities by week. You set up your chart and have a view that includes data from the last 5 weeks.
The only problem is, that a view showing the last 5 weeks doesn’t show the last 5 weeks. It is showing you the last 5 x 7 days, or 35 days. If those 35 days end on a Thursday, your chart, showing you information by week, will only have data from two days of that week. In other words, the chart will (inaccurately) present you as a slacker that week. We all know that is not the case, so we will need to make sure that those charts have an accurate representation of weekly data.
To correct this, I will create a view with 6 weeks of data, but limit the chart to show only the last 5 weeks. That will ensure that all columns in the chart represent a complete week’s data.
Note: This approach also works for months. However, if your fiscal periods are aligned with months, a View with the last 5 fiscal periods, will actually be the last 5 full months. A view with the last 5 months, will be the last 5 x 30 days = 150 days.
First, I’ll create the weekly chart in the editor and then export the chart xml.
I’ll start by adding an order clause, and reverse the standard order. The descending=”true”, means the most recent week will now be the first item, rather than the last.
To remove the sixth straggler week, I’ll add Maximum=”5.75” to the Axis X. This will ensure that I am only displaying the first 5 items and not the 6th. This is why I had to change the order of the weeks.
Why set the maximum at 5.75 and not just 5. If set to 5, the maximum property would cut the chart exactly at 5, which would be the middle of the 5th column at the interval mark, highlighted below. If maximum was 5, only half a column would be displayed.
Now we have a chart where all the weeks have a complete set of data. The exception of course, is the current week, which depending on the day might not be complete. That however, makes much more sense to the user.
If you want the oldest week to the left, add IsReversed=”True” to the X axis properties.
Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter Follow @crmchartguy