Display only complete weeks or months in charts

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.

MS CRM chart with incomplete week highlighted

Incomplete week highlighted in chart

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.

Add order clause to MS CRM chart xml. order alias="start" descending="true"

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.

Maximum added to X axis in the MS CRM Chart xml

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.

MS CRM Chart activities by week, displaying only complete weeks by modifying the chart xml.

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.

X Axis is reverased by adding IsReversed="true" to the xml for the MS Dynamics CRM chart.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

Advertisements

, , , , ,

  1. #1 by Kyle Andersen on October 27, 2014 - 7:32 pm

    I’m a little confused… why do you add the descending=”true” and then seem to counter it by adding an IsReversed=”true”?

    I couldn’t get the code to be accepted by 2013 SP1 (invalid xml, it said). It seems to work just fine without it, however…

    Thanks. Just curious what the intended purpose is.

    • #2 by Kyle Andersen on October 27, 2014 - 7:33 pm

      xml typed into comments was swallowed by the internet. The code I’m referring to not being accepted the order alias=”start” descending=”true”.

      • #3 by Kyle Andersen on October 27, 2014 - 7:35 pm

        Nevermind, reread your post. Makes the maximum work as intended. Got it.

  2. #4 by Lord on January 28, 2016 - 11:21 am

    Nice work. However, I’ve this concern e.g. if week no. 3 doesn’t have any data in that case, it will end up showing the 6th week. Do you’ve any solution for that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: