Summarize Current Year vs Last Year by month
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20664
Printed Date: 28 Apr 2024 at 3:42pm
Topic: Summarize Current Year vs Last Year by month
Posted By: chudok
Subject: Summarize Current Year vs Last Year by month
Date Posted: 21 Apr 2014 at 7:05am
I am trying to have a user enter in a date parameter. they will enter in for a 2 year range. I am trying to return data into (CY Jan, LY Jan), (CY Feb, LY Feb) etc....
When i break out the dates by month and year it won't let me summarize or get a running total for each month.
I cannot figure out how to get this to work and give me a total for each month.
|
Replies:
Posted By: otto
Date Posted: 21 Apr 2014 at 7:31am
I am not sure if is this what you want, but you can create a formula like below for each month and then do the summary
if {TABLE.DATE} >= dateSerial ((Year (CurrentDate)),(Month (CurrentDate)) ,Day (Minimum (LastFullMonth))) and
{TABLE.DATE} <= dateserial(year(currentdate),month(currentdate),1-1)
then {Field to be summarized} else 0
this one will returns previews month data
|
Posted By: chudok
Date Posted: 21 Apr 2014 at 8:10am
if {GL_DetailPosting.PostingDate}>=Date(year(minimum({?Date Range}))+1,Month(minimum({?Date Range})),2) and
{GL_DetailPosting.PostingDate}<=maximum({?Date Range}) and month({GL_DetailPosting.PostingDate})= 1 then {@Amount}
this is working for my current year data, I just can't figure out the formula to get last years data by month......
|
Posted By: otto
Date Posted: 21 Apr 2014 at 8:46am
I am not sure if this one is the best way, but is the way that I did it once in a 13 month trend report I had to do. I created @EndDate(Maximum{?Date Range}) and @StartDate(Minimum{?Date Range}) formulas that storage Maximum and Minimum date range of my date parameter. Then I created 13 formulas, one per month with this format and placed on details section.
this will capture your last month(@EndDate)
you can call this formula Month24 b/c you will need 24 month of data, right?
if {GL_DetailPosting.PostingDate} >= DateSerial ((Year ({@End_date})),(Month ({@End_date})) ,Day (Minimum (LastFullMonth))) and
{GL_DetailPosting.PostingDate} <= {@End_date}
then {@Amount} else 0
Next you will have to create another formula @Month23
if {GL_DetailPosting.PostingDate} >= DateSerial ((Year ({@End_date})),(Month ({@End_date})-1) ,Day (Minimum (LastFullMonth))) and
{GL_DetailPosting.PostingDate} <= dateserial (year({@End_date}),month({@End_date}),1-1)
then {@Amount} else 0
Then another @Month22
if {GL_DetailPosting.PostingDate} >= DateSerial ((Year ({@End_date})),(Month ({@End_date})-2) ,Day (Minimum (LastFullMonth))) and
{GL_DetailPosting.PostingDate} <= dateserial (year({@End_date}),month({@End_date})-1,1-1)
then {@Amount} else 0
and the same with other month so the last one will be
if {GL_DetailPosting.PostingDate} >= DateSerial ((Year ({@End_date})),(Month ({@End_date})-24) ,Day (Minimum (LastFullMonth))) and
{GL_DetailPosting.PostingDate} <= dateserial (year({@End_date}),month({@End_date})-23,1-1)
then {@Amount} else 0
That will capture your amount by month
|
Posted By: chudok
Date Posted: 21 Apr 2014 at 8:50am
These formulas won't let me return a sum total. that is where I keep falling into issues...
|
Posted By: otto
Date Posted: 21 Apr 2014 at 9:00am
|
do you need the whole year summarized in one formula or split it by month to compare it with a year before?
|
Posted By: chudok
Date Posted: 21 Apr 2014 at 9:10am
|
I need to summarize each month and compare current month-to-date to prior-month-to-date.
|
Posted By: otto
Date Posted: 21 Apr 2014 at 9:15am
|
are you grouping on the report?
|
Posted By: DBlank
Date Posted: 21 Apr 2014 at 9:17am
IMO just group on the month and then the year inside the month
datepart('m',GL_DetailPosting.PostingDate)
datepart('yyyy',GL_DetailPosting.PostingDate)
You can then do a sum at the year footer or use a crosstab using these two fields.
if you just want 12 parenthesized values in the report footer just create 24 running totals (the crosstab is much easier) with different evaluation formula.
Jan last year eval criteria
datepart('m',GL_DetailPosting.PostingDate)=1 and
datepart('yyyy',GL_DetailPosting.PostingDate)= year(currentdate)-1
Jan this year
datepart('m',GL_DetailPosting.PostingDate)=1 and
datepart('yyyy',GL_DetailPosting.PostingDate)= year(currentdate)
|
Posted By: chudok
Date Posted: 22 Apr 2014 at 2:09am
I don't necessarily need current year for my report. they be comparing and looking back to the 2 prior years.... so how can i incorporate the year from a Parameter field?
This works for my current year - but for PY I can't get a formula to work and to summarize...... any thoughts?
if {GL_DetailPosting.PostingDate}>=Date(year(minimum({?Date Range}))+1,Month(minimum({?Date Range})),01) and
{GL_DetailPosting.PostingDate}<=maximum({?Date Range}) and month({GL_DetailPosting.PostingDate})= 1 then {@Amount}
|
Posted By: DBlank
Date Posted: 22 Apr 2014 at 3:20am
if you just need the prior two years why are you using a parameter?
your select would just use the currentdate to determine the prior years as your select statement. Then you can group on the month part then the year part as indicated earlier.
datepart('yyyy',{GL_DetailPosting.PostingDate}) in [datepart('yyyy',currentdate)-1,datepart('yyyy',currentdate)-2]
|
Posted By: chudok
Date Posted: 23 Apr 2014 at 1:49am
Here's the issue. I don't know what 2 years they will want to run this report. So it using current date really does not work for me. They might want to run it right after each other for different years. (2013 and 2014) then (2013 and 2012). They also might run it in January 2015 for the years 2014 and 2013.
I wish I could use current date - but then want a date parameter to use...
Is there a way I can do this and still get it to summarize?
|
Posted By: DBlank
Date Posted: 23 Apr 2014 at 4:17am
|
Sorry to confuse the matter. I misread the posting.
so your user can enter 2 dates (1 paramter set to a range).They are supposed to enter 2 full years?
What do you want it to do if it is not 2 full years?
If it is less?
or more?
or say they enter "Feb 10 2010" to "Feb 10 2012" which has 3 actual year values in it? How do you want to compare these?
|
Posted By: lockwelle
Date Posted: 23 Apr 2014 at 5:08am
why not just limit them to 1 date to enter...the begin or the end date, and have the report calculate the other date?
just a thought
|
Posted By: DBlank
Date Posted: 23 Apr 2014 at 5:20am
|
I would agree with Lockwelle, but the answer to my questions about what happens when users enter other ranges may exclude that as an option. Also if you are looking at full calendar or fiscal years I would even consider using a single numeric param for a year or a preset drop down for fiscal year selection.
That said, the ability to group on and display summarizations based on the param values should be easy enough. However to do it properly you will need to decide on these possible parameter entry issues and how the report should handle them.
|
Posted By: chudok
Date Posted: 23 Apr 2014 at 5:43am
I understand the concern. I decided to use a different reporting platform that I have instead. This has become more complicated that I needed. Also if someone wants to look at current year normally, but say Jan 5th, they want to see end of prior year data they can't because of the the formulas.
thanks for all the help
|
Posted By: chudok
Date Posted: 24 Apr 2014 at 7:41am
I do have a quick question. I've tried to research this but I cannot find the answer.
What does the 01 do in the formula below?
Date(year(minimum({?ReportDate})),Month(maximum({?ReportDate})),01)
|
Posted By: DBlank
Date Posted: 24 Apr 2014 at 7:43am
|
uses the first day of the month
DATE(yearvalue,monthvalue,dayvalue)
|
Posted By: chudok
Date Posted: 24 Apr 2014 at 7:59am
so if I have this formula.....
if {ARN_InvHistoryHeader.InvoiceDate}>=minimum({?ReportDate}) and
{ARN_InvHistoryHeader.InvoiceDate}<=Date(year(maximum({?ReportDate}))-1,
Month(maximum({?ReportDate})),day(maximum({?ReportDate})))
and my data parameters are 01/01/2013 and 04/22/2014..... shouldn't my results be dates between 01/01/2013 and 04/22/13
I am trying to get Prior Year to date total up to the maximum date (minus 1 year) selected
|
Posted By: DBlank
Date Posted: 24 Apr 2014 at 8:14am
|
so you have one parameter (ReportDate) as a date type that allows for a range to be entered.
For you example, your range in that one parameter is using 01/01/2013 to 04/22/2014
your if formula is evaluating to TRUE if your invoice date is between
01/01/2013 and 04/22/2013
an easier way to do it is
{ARN_InvHistoryHeader.InvoiceDate} in minimum({?ReportDate}) to dateadd('yyyy',-1,maximum({?ReportDate}))
|
|