Solved

Period to date and Year to date

Posted on 2011-02-15
7
274 Views
Last Modified: 2012-05-11
I need to get the period to date as well as year to date  charge amounts  by a program.
I am using @ToDate  to retrieve the data from SQL.  I am not quite sure how my formulas should look. I created this formula for the period charges but it just brings back zeros
if date(Minimum ({TPr_ProcedureReimbursement;1.DateTrans},{@Group Program} )) in Cdate({?From Date})  to Cdate ({?@todate})

then Sum ({TPr_ProcedureReimbursement;1.Charges}, {@Group Program})
else 0
This is what I created for year to date
if date(Minimum ({TPr_ProcedureReimbursement;1.DateTrans}, {@Group Program})) in date(Year({?@todate})-1,1,1) to DateAdd('y',-1,{?@todate})

then Sum ({TPr_ProcedureReimbursement;1.Charges}, {@Group Program})
else 0
This is ignoring the date and jusr sums al charges by program
Any guidance would be appreciated.
0
Comment
Question by:SequestTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34903318
Are you using this in Crystal or in the SQL?

Your selection formula should be

{TPr_ProcedureReimbursement;1.DateTrans} >= date(Year({?@todate}),1,1)  AND
{TPr_ProcedureReimbursement;1.DateTrans} <= ,{?@todate}

Why were you subtracting 1 from the year?
Are you tring to get last year's data?

Is ToDate a range?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 34914828
Are you using a stored procedure or just reading tables directly in your report or ...?  As mlmcc mentioned, the record selection in your report, or the WHERE in your stored procedure, needs to include the whole year (however you define that) for the YTD part.

 As for your formulas, it depends on what {?From Date} and {?@todate} are, and what data the report is reading, but trying to use Minimum is probably at least part of the problem.

 I'm guessing that {?From Date} and {?@todate} are the starting and ending dates for the period.

 For the period formula:
 The report will have to read data for the whole year to calculate the YTD total.  That means that the minimum date for each {@Group Program} will probably be some time earlier in the year (before the current period), so the minimum date won't be between {?From Date} and {?@todate}, so you get 0 for the period formula.


 For the YTD formula:
 > This is ignoring the date and jusr sums al charges by program

 That would normally be what you want, if the report is only reading the data for the current year (up to {?@todate}).  If the report includes data after {?@todate}, the formula will be comparing the minimum date for each {@Group Program} to see if it falls in the year range, and if it does, it does a sum of _all_ of the records for the group.

 Perhaps you're thinking that the date test in the if-else is applied to the Sum function.  It is not.  Sum ({field1}, {group field}) always returns the total for {field1} from every record in the group.


 If you can have the record selection (or the WHERE in your stored procedure) limit the records to just the ones in whatever you consider to be the "year to date", then you can just do a simple summary of Charges to get the YTD total.  If you need to include records with dates outside of the "year to date", you can use a formula similar to the one below for the period.

 For the period total, create a formula like the following and do a summary for the group on that formula:

if Date({TPr_ProcedureReimbursement;1.DateTrans}) in
 Cdate ({?From Date})  to Cdate ({?@todate}) then
  {TPr_ProcedureReimbursement;1.Charges}


 James
0
 

Author Comment

by:SequestTech
ID: 34965124
Don't close. I am just getting back to this report and i need to evaluate the answers that I receievd.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35224163
You are telling it to sum all the records in the group not just those that meet your date criteria.

Try something like

if date(Minimum ({TPr_ProcedureReimbursement;1.DateTrans}, {@Group Program})) in date(Year({?@todate})-1,1,1) to DateAdd('y',-1,{?@todate}) then
   {TPr_ProcedureReimbursement;1.Charges}
Else
   0

Similarly for the other formula.

You can then use a running total on the formula to get the total.

Crystal Summary funtion work on the entire recordset or the specified group.  You probably have records in the group that don't meet the date criteria.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35399255
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question