Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Chart that compares Quarter/years... Post Reply Post New Topic
Author Message
carmstrong
Newbie
Newbie
Avatar

Joined: 25 Feb 2010
Online Status: Offline
Posts: 3
Quote carmstrong Replybullet Topic: Chart that compares Quarter/years...
    Posted: 25 Feb 2010 at 10:08am
I need to create a chart that compares data from multiple date ranges.

I have data from Q1 of 2009 and would like to compare data of Q1 2010 on the same chart to see the change from one year, in the same period, to the other.

I can create the chart for Q1 of either 2009 or 2010 but each is in a separate document because I am filtering for that quarter.

Not sure how to superimpose data of Q1 of 2 different years on the same chart.


Thanks!
C.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2010 at 12:13pm
I am confused by your question. If the problem is that you are filtering by a quarter why not just change your filter to include both?
E.g. table.date > some date here and datepart('q',table.date)=1


Edited by DBlank - 25 Feb 2010 at 12:22pm
IP IP Logged
carmstrong
Newbie
Newbie
Avatar

Joined: 25 Feb 2010
Online Status: Offline
Posts: 3
Quote carmstrong Replybullet Posted: 25 Feb 2010 at 1:37pm
If I understand you correctly then the formula  you gave will give me data for the first quarter of the year that I have designated in the "able.date > some date" which is great for getting that data in a formula (I had not used  'datepart' before or knew of it so thank you!)

What I am looking for is to chart something like:

{TICKET.REPORTDATE} > DateTime (2009, 01, 01, 00, 00, 00) and datepart('q',{TICKET.REPORTDATE})=1

AND

{TICKET.REPORTDATE} > DateTime (2010, 01, 04, 00, 00, 00) and datepart('q',{TICKET.REPORTDATE})=1

where each is a weekly line graph for the first quarter of each year (2009 & 2010) both in the same chart, in order to compare the two.

I can get a weekly graph of the first quarter for each year, but not for the first quarter of both years together in the same chart.

I hope I was clearer with my description this time. 

Thank you for your reply.

C.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2010 at 1:41pm
Just do one select statement with the 2009 date...
{TICKET.REPORTDATE} > DateTime (2009, 01, 01, 00, 00, 00) and datepart('q',{TICKET.REPORTDATE})=1
 
It will pull all records from 09 and 10 (first quarter only).
Then you can group on the date set to year and it would be side by side
 
IP IP Logged
carmstrong
Newbie
Newbie
Avatar

Joined: 25 Feb 2010
Online Status: Offline
Posts: 3
Quote carmstrong Replybullet Posted: 25 Feb 2010 at 2:26pm
Right now they show sequentially as 1 long line starting in Jan '09 and ending in Mar '10

How can you do it to where you have 2 lines stacked, both starting on Jan first and ending in March.

When I use a formula in the Chart Expert it gives me the data as 1 point of data (final number) in a  true/false set and not weekly points along a date chart.


    |    /\
    |  /   \/\     
    |/         \   /\  2009
    |  / \      \/
    |/     \      /\   2010
    |         \  /
    |_____\/___________________  Weekly points graphed
     Jan          March 


sorry for the simplistic pic, it was easier to describe like this than in words.

C.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2010 at 2:49pm
Sorry, thought you were doing a bar chart.
 
Here is one way (although I do not know how elegant it is. (I am betting there is a much nicer approach but my brain is fried right now).
1. Create a formula called 'Week' as:
datepart('ww',{TICKET.REPORTDATE})
Create a group on this formula field.
Create a Running Total:
Name=2009
Field to summarize=field you want displayed on chart
Type of summary= you choose (noy sure if you are doing a count or a SUM)
Evaluate= use a formula
Year({TICKET.REPORTDATE})=2009
Reset=On change of group1
 
Create a second RT:
Name=2010
Field to summarize=field you want displayed on chart
Type of summary= you choose (noy sure if you are doing a count or a SUM)
Evaluate= use a formula
Year({TICKET.REPORTDATE})=2010
Reset=On change of group1
 
Create your chart type as Stacked Line Chart (with or without markers)
Data= On chaage of @Week
Show values = add both yuor RTs (#2009 and #2010)
 
Validate the data


Edited by DBlank - 25 Feb 2010 at 2:52pm
IP IP Logged
AbsoluteWhite
Newbie
Newbie
Avatar

Joined: 31 Oct 2014
Location: United States
Online Status: Offline
Posts: 1
Quote AbsoluteWhite Replybullet Posted: 24 Nov 2014 at 9:36am
4 years later...

I used this answer to create a year-over-year chart to look at data and it works great. I have a 2014 and 2013 trendline on the same chart. The only issue I'm running into is that my 2014 trendline goes to 0 for the month where there is no data yet (November 2014).

How can I just have the 2014 trendline discontinue where the data stops (Oct 2014) instead of it dropping down to 0?

I tried checking the box for Chart Options > break lines for missing data but no luck. Any suggetions??

Thanks!!

Edited by AbsoluteWhite - 24 Nov 2014 at 9:39am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.