Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: compare with same period last year Post Reply Post New Topic
Author Message
jingle
Newbie
Newbie


Joined: 20 Jul 2010
Location: Denmark
Online Status: Offline
Posts: 3
Quote jingle Replybullet Topic: compare with same period last year
    Posted: 20 Jul 2010 at 6:33am
Hi All

i at beginners level in CR 2008 and have a problem.
i need to make a report that compares sales figures year to date with the same period last year.

I managed to get my selection formula correct.
{INVOICE_DATE} > Dateadd ('d',1,(currentdate) - (datepart("y",currentdate)))
or
{INVOICE_DATE} in dateadd('yyyy',-1,Dateadd ('d',1,(currentdate) - (datepart("y",currentdate)))) to Dateadd('yyyy',-1,(currentdate))
but how do i group on the date so i get this layout

period revenue
2009-01 2154
2010-01 2568

2009-02 1487
2010-02 1458

and so on.   
Hope someone can point me in the right direction       
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jul 2010 at 7:31am
you can trim your select statement as you want all records for 2 years as of of tomorrow, correct?
{INVOICE_DATE} > dateadd(,'yyyy',-2,currentdate)
For grouping it is trickier
DO you need it to start at the earliest month (2 years ago tomorrow) or do you want it to start 2 years ago Jan?
IP IP Logged
jingle
Newbie
Newbie


Joined: 20 Jul 2010
Location: Denmark
Online Status: Offline
Posts: 3
Quote jingle Replybullet Posted: 20 Jul 2010 at 7:41am
if i do your selection formula i get a lot of records that i dont need, i only need last years records to this date, but last year offcourse.

i need it to start in jan last year.
earliest month (2 years ago tomorrow) or do you want it to start 2 years ago Jan
isnt this the same?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jul 2010 at 7:53am
My mistake.
I was making an assumption you were going back 24 months not to jan.
 
 
{INVOICE_DATE} in yeartodate
or
{INVOICE_DATE} in lastyearytd
 
Make a formula field as
totext(month({INVOICE_DATE}),'00') + ' ' + totext(year({INVOICE_DATE}),0,'')
 
Group on that for your order to work.
You can make an inverted one to display as year month
IP IP Logged
jingle
Newbie
Newbie


Joined: 20 Jul 2010
Location: Denmark
Online Status: Offline
Posts: 3
Quote jingle Replybullet Posted: 20 Jul 2010 at 8:09am
that did the trick. Thanks a lot.
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.031 seconds.