How do I create a CRM 2011 Chart showing comparison of revenue between this year and last year
Question
Answers
-
No problem at all :) I created two fields on the opportunity entity:
Field Name Type Values
Month of Close Option Set January through December
Year of Close Whole Number Min Value = 2011 Max Value = 2030
I then created two workflows:
Set closing Month
This was set to run when the status changed and was at an organisation level. I also set it to be on-demand only so I could manually run the workflow to test it
My steps were:
- Check condition: If the status was set to Open, blank out the Month of Close field
- Otherwise: If the closing date was on or after 1st January AND If the closing date was on or before 31st January
- Update Record: Set Month of Close to "January"
- I repeated step 3 for every month (you will need to create one for each year too)
Set closing Year
This was set to run when the status changed and was at an organisation level. I also set it to be on-demand only so I could manually run the workflow to test it
My steps were:
- Check condition: If the status was set to Open, blank out the Year of Close field
- Otherwise: If the closing date was on or after 1st January AND If the closing date was on or before 31st December
- Update Record: Set Year of Close to "2012"
- I repeated step 3 for every year I needed (you will need to create one each year manually)
I then ran the workflows on a test set of data - this populated the month and the year. I could then create the chart which looked like this:
I could then see the chart I showed you all in the previous post. Although it was a little lengthy to do, it seems to work OK. I hope this helps
Sharan
- Proposed as answer by F. Schulz Tuesday, September 4, 2012 6:28 AM
- Marked as answer by Rob BoyersEditor Wednesday, October 31, 2012 9:56 PM
All replies
-
Thanks for the response Carsten. Unfortunately neither Goals nor this blog address the requirement I have. I apologise if I haven't been as clear as I tried to be. I need to see the same data (actual revenue) for different years broken down by month. I have mocked up an image on the basis that a picture paints a thousand words. As you can see, it contains two columns for each month - one being last years revenue and one being this years revenue.
The view to support the chart would contain all closed oportunities where the Actual Close date is This Fiscal Year OR Last Fiscal Year
I want the chart (via XML I'm assuming) to allow dategrouping of specific fiscal years (it allows for fiscal year but this only shows two columns - one for each year and you cannot split it further into months). I was hoping that somebody would suggest that I have a fetch attribute set for each year - something similar to this....
- groupby=true, alias=actualclosedate, dategroup=lastfiscalyear
- groupby=true, alias=actualclosedate, dategroup=thisfiscalyear
- groupby=true, alias=actualclosedate, dategroup=month
In other words have two groupby's - one for this year and one for last year and force the final groupby to be by month so I display the total value for each year against each month.
My understanding of Goal Management is that it shows comparitive data on a single chart but this appears to be coded so the XML shows attributes containing the fields such as "computedtargetasoftodaymoney" (nothing related to period).
Mu understanding of the blogs such as the one you mention show how to create multiple series of different data for the same time period e.g. Actual Revenue compared to Estimated Revenue
If I have misunderstood or am not applying either of your two suggestions correctly, please let me know as I would dearly like this to be a simple solution.
Thanks!
-
Rahul,
I understand how the principal of what you are doing but I can't get the same result. When I put in the custom field of year, and use this as the second category on the x axis (in online CRM) then all I get is the different years coloured as different series, but not as Sharon was looking for. See below:
Did you do something to the chart XML in an editor, if so what, or have I missed something here.
Thanks in advance
Geoff
-
I experienced the same as Geoff so I spent some more time on this today. This time I created a second field on the opportunity entity to hold the month of closing. I can now group by the year (new field) and the month (new field).
Just so you're aware, both the year and the month fields are updated using workflows - it was lengthy (!) to create a workflow for the months as I had to create a conditional branch for every month within each year in order to specify the range of dates that should be affected (the workflow conditions are all AND statements so I couldn't put "On or After 1st January 2011 AND On or Before 31st January 2012 as well as the conditions for 2012" in one condition statement - I had to have one for each year :).
If any of you want the workflows and/or field setups, just let me know and I'll push them out here.
Sharan
-
No problem at all :) I created two fields on the opportunity entity:
Field Name Type Values
Month of Close Option Set January through December
Year of Close Whole Number Min Value = 2011 Max Value = 2030
I then created two workflows:
Set closing Month
This was set to run when the status changed and was at an organisation level. I also set it to be on-demand only so I could manually run the workflow to test it
My steps were:
- Check condition: If the status was set to Open, blank out the Month of Close field
- Otherwise: If the closing date was on or after 1st January AND If the closing date was on or before 31st January
- Update Record: Set Month of Close to "January"
- I repeated step 3 for every month (you will need to create one for each year too)
Set closing Year
This was set to run when the status changed and was at an organisation level. I also set it to be on-demand only so I could manually run the workflow to test it
My steps were:
- Check condition: If the status was set to Open, blank out the Year of Close field
- Otherwise: If the closing date was on or after 1st January AND If the closing date was on or before 31st December
- Update Record: Set Year of Close to "2012"
- I repeated step 3 for every year I needed (you will need to create one each year manually)
I then ran the workflows on a test set of data - this populated the month and the year. I could then create the chart which looked like this:
I could then see the chart I showed you all in the previous post. Although it was a little lengthy to do, it seems to work OK. I hope this helps
Sharan
- Proposed as answer by F. Schulz Tuesday, September 4, 2012 6:28 AM
- Marked as answer by Rob BoyersEditor Wednesday, October 31, 2012 9:56 PM
