Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: DateAdd Function Post Reply Post New Topic
<< Prev Page  of 2
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jul 2010 at 8:23am
can you tell me what COlumns B, C and D are?
My initial thought is to either use a crosstab in the report footer or to group on the date field, do calucaltions at the group footer and suppress the group header and details. A lot easier and dynamic based on a entry param.
Basically you only need one date param. I assume you want the full 12 months from that param
Your select statement would be
datefield in dateserial(year({param},month({Param},1-1) to dateserial(year({param}-1,month({Param}-1,1)
IP IP Logged
keithrichards
Newbie
Newbie


Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote keithrichards Replybullet Posted: 15 Jul 2010 at 1:38pm

Before I answer your question I just want to mention that the report is currently grouped on a fild called VEHICLE_VIN. Ok now that thats out of the way.

 

COL B is a RT called SoldUnitsJunCIW that does a Distinct Count on a field called VEHICLE_VIN and has the following formula incorporated. Basically it adds up how many cars were sold for the month and becomes the benchmark to calculate the percentages;

 

{DEL_DATE} in Date (2009,06,01) to Date (2009,06,30)

 

COL C is a formula field called 4_ServicedvehicleJunCIW that contains the following;

 

WhilePrintingRecords;

NumberVar CountReportJUNCIW := CountReportJUNCIW

 

COL D is a formula field called 5_Retention%JuneCIW that contains the following;

 

{@4_ServicedvehicleJunCIW}/{#SoldUnitsJunCIW}*100

 

There are actually 5 steps in the process COL C being the result of step 4 and COL D being the result of step 5. I will list steps 1-3 below so you can see the flow.

 

#########################################

 

Step 1. The first formula field is called 1_RoDateJunCIW and contains the following;

 

If {vehicle_inventory1.DEL_DATE} in Date (2009, 06, 01) to Date (2009, 06, 30) and

 

{service_retention1.RO_DATE} in Date (2009, 07, 01) to Date (2010, 06, 30) then {service_retention1.RO_DATE}

 

Else Date(1900, 01 ,01)

 
If the data is within the period that I want it lists the dates or for those outside returns 01/01/1900.
 
Step 2. Is a formula field called 2_RoDateMaxJuneCIW containing the following;

 

NthLargest(1,{@1_RoDateJunCIW},{vehicle_inventory1.VIN})

 
I will get multiple results from step 1 and as I only want to count a single instance I use the NthLargest to throw out a single result.
 
Step 3. Is a formula field called  3_RoCountJunCIW containing the following;

 

WhilePrintingRecords;

 

NumberVar CountJUN;

NumberVar CountReportJUN;

 

 

If {@2_RoDateMaxJuneCIW} in Date (2009, 07, 01) to Date (2010, 06, 30) and

 

DateDiff ("d", {vehicle_inventory1.DEL_DATE}, {@2_RoDateMaxJuneCIW}) <= 365 then

 

(CountJUN := CountJUNCIW + 1;

 

CountReportJUNCIW := CountReportJUNCIW + 1)

 
This counts each result from step 3 should it meet the criteria.
 
So basically I have these five steps for each one month. So as you can see I am a victim of managing a stack of dates if I want to change the reporting periods.

 

 

 



Edited by keithrichards - 15 Jul 2010 at 1:42pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jul 2010 at 3:23am
Sorry, having touble following all the formulas.
This VIN report has been a pain for you, eh?
Can you explain in logical data terms exactly how these should be counted with a few sample rows to support the logic?
I get you want to analyse 1 year of data, for the last full 12 months
you need to count per month the number of sales for the month but then I get lost on the max date issues?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jul 2010 at 3:25am
also here is a link to a similar question without the max date issue.
maybe it will help???
IP IP Logged
keithrichards
Newbie
Newbie


Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote keithrichards Replybullet Posted: 16 Jul 2010 at 9:13am
Hi there BB,
 
Thanks for the URL to the article.
 
Your final solution to that member seems to be the same as what I am trying to achieve. Over the week end I'll create a sample report using similar changes and drop you a line on how I go.
 
Yes it's been a bit challenging..... but satisfying!
 
Thanks for taking the time to try and figure out all my hacking!
 
cheers,
 
KR
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Jul 2010 at 11:13am
Good luck and glad to hear you are enjoying the challenge. That is refreshing.
Thumbs%20Up
I will try to assit if needed
IP IP Logged
keithrichards
Newbie
Newbie


Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote keithrichards Replybullet Posted: 20 Jul 2010 at 12:15pm

Hi thereDB,

 

I did have to delete the original running totals I created because there was some weird buggy thing happening. But when I added the following formula to newly created running totals all is working fine;

 

{DEL_DATE} in DateAdd ('m', -0,{?Start Month Deliveries}) to DateAdd ('m', -0,{?End month Deliveries}).

 
I do have another query I but I'll raise another thread as it a s lightly different topic.
 
Thanks again.
 
KR

 



Edited by keithrichards - 20 Jul 2010 at 12:15pm
IP IP Logged
<< Prev Page  of 2
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.