Hi there,

 

I have am trying to use the DateAdd function to extrapolate a date off a parameter field.

 

When I create a second DateAdd formula to extrapolate a second date of the same parameter fields, it changes the original result created from the first formula to that of the second formula.

 

 

 

There are two Parameter Fields;

 

Start Delivery Month [let’s say the value is 01/06/2009]

End Delivery Month [let’s say the value is 30/06/2009]

 

I have two Formula called;

 

1.    Date Add 1 Start Date

 

2.    Date Add 1 End Date

 

Date Add 1 Start Date contains the following syntax

 

DateAdd ("m", 0, {?Start Delivery Month})

 

Date Add 1 End Date contains the following syntax

 

DateAdd ("m", 0, {?End Delivery Month})

 

I then have a Running Total called DelMonth1 which does a distinct count and contains the following formula;

 

{DEL_DATE} in {@ Date Add 1 Start Date} to {@ Date Add 1 End Date}

 

The running total is then placed in the footer and up until this point I get the correct result, as all that is happening is that I am bringing across the original parameter dates via the formula and getting data from June 2009.

 

1.    Date Add 2 Start Date

 

2.    Date Add 2 End Date

 

Date Add 1 Start Date contains the following syntax

 

DateAdd ("m", -1, {?Start Delivery Month})

 

Date Add 1 End Date contains the following syntax

 

DateAdd ("m", -1, {?End Delivery Month})

 

 

I then have a second Running Total called DelMonth2 which is place in the footer which does a distinct count and contains the following formula;

 

{DEL_DATE} in {@ Date Add 2 Start Date} to {@ Date Add 2 End Date}

 

This is supposed to bring back a result based on the parameter field dates minus one month [should return data from May 2009]. I don’t actually get the correct result, but what happens next is really weird.

One more thing.

 

So So what I am actually trying to acheive is to have one set of parameter fields that give me a starting month, and then us the dateDiff function to bring back the other 11 months worth of data.

 

Of course my other option is to have a total of 24 parameter fields and individully select the start date month and end date month for each month.... but I was trying to get away from that.

 

Thanks KR

First you have to get your select statement to grab 1 year of data. I am not sure if you want one year froma specific date or 12 full months from a specific date (entered by a user as a parameter).

Once you have that you can get the monthly breakdown by grouping on the date field or using a crosstab or using running totals depending on what you need to accomplish.

to get the last year from a date

to get 12 full months would be eaiser to use dateserial function.

Does this help?

 

Thanks for your feedback.

 

I am using running totals to get the monthly breakdown and because the data is coming from different tables the gouping option is not feasable.

 

I tried your suggetsion in the running total again and I'm getting that weird situation where the running total fields are synchonized [that means when I change the formula in one, it automatically updates the other with the same changes].

 

So I thought as a work around I would just create a formula field to do the same thing. But I am having problems with the correct syntax. Are you able to figure out how I should mix this around so it works?

 

 

DistinctCount ({vehicle_VIN}) in ({DEL_DATE}) Date (2009,04,01) to Date (2009,04,30) 

 

Thanks again,

 

KR

I have never seen 'synchronized' formulas unless you are using shared variables.

Your formula is fine from a SQL standpoint but won't work in crystal and there is really no tweak of it.

Generally that is where Running Totals come in.

First I want to make sure you are using a Crystal Running Total function and not a Variable formula that you are calling a running total (as oftne peopel will do that).

For the Crystal Running Total this is how your static would dates above work.

NAme=TestRT

Field to SUmmarize=Vehicle Vin

Summary=DistinctCOunt

Evaluate=use a formula

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

Reset=Never

Place in the report footer for a total display (RTs do not work in headers)

 

Thanks for your replys!

 

I Agree with all that you are saying and can confirm that the different tables are joined correctly and the grouping is working.

 

With regard to the Running Total question, yes I am using the Crystal Running Totals functionality [and not a formula] which is placed in the footer.

 

Your example below is right on the money. This is exactly what I currently have.

 

The only aspect I am having a problem with is when I change the following;

 

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

 

To something like this

 

{DEL_DATE} in DateAdd ('yyyy', -1,{?Start Delivery Month} to {?End Delivery Month}

 

So in effect swapping the hard coded dates with a set of parameter fields that work with the DateAdd function. That way I can have one set of parameter fields with different dateAdd criteria loaded into different running total fields.

 

So although each RT field is using the the same parameter field, each RT will work off a different date range because of the DateAdd function.

 

In the examples below RT 1 would total data for December, RT 2 would total data for November and RT 3 would total data for October. That's my theory anyway!!

 

RT 1

 

{DEL_DATE} in DateAdd ('yyyy', -1,{?Start Delivery Month} to {?End Delivery Month}

 

RT 2

 

{DEL_DATE} in DateAdd ('yyyy', -2,{?Start Delivery Month} to {?End Delivery Month}

 

RT 3

 

{DEL_DATE} in DateAdd ('yyyy', -3,{?Start Delivery Month} to {?End Delivery Month}

 

 

Given I am having problems It may seem that Crystal does not let you do this from within the Running Total Formula section?

 

Thanks again,

 

KR

 

1. Are you using your Params in the Select expert at all?

2. If i remember correctly Datediff and Dateadd do not like the format of dd/mm/yyyy but rather want it as mm/dd/yyyy (This may be the root of all your problems)

3. basically are you trying to allow a user to enter value for a month and then you compare the totals for each year for that month only? I can give you a different approach for that that might be easier...

 

Yes the params are being used in the select expert. 

 

To answer your 3rd item I have listed a sample of the output which sits in the footer of the report [the group and details sections which contain the formulas to transition the data are suppressed]. Col A, C, D are formula fields and COL B is a RT.

 

I want the user to enter a value for a month [let's say in the example below the params would be set for Jun 2010]. The output of the result would show like you see below. The user only has to enter one date range but get the 12 months.

 

I should also point out that at the moment that to get the data to line up in each month as below I have date ranges sitting in the formula fields that reside in the details section.

 

My problem now is that if I want to change the report to say start at July 2010, I will have to change dates in all the formulas through out the report [probably 40 places].... hence all my sleepless nights!

 

Hope this helps.... and thanks again!

 

 

COL A            COL B             COL C          COL D                  

                                                                  

                                                                                                 

  Jun‑10          3,765           3,044.00          80.85%            

 

  May‑10         2,901           2,265.00          78.08%            

 

  Apr‑10          2,303           1,836.00          79.72%            

 

  March‑10       2,786           2,184.00          78.39%            

 

  Feb‑10          3,068           2,462.00          80.25%            

 

  Jan‑10          3,419           2,699.00          78.94%            

 

  Dec‑09          3,106           2,491.00          80.20%            

 

  Nov‑09          2,739           2,112.00          77.11%            

 

  Oct‑09          2,763           2,116.00          76.58%            

 

  Sept‑09         3,254           2,435.00          74.83%            

 

  Aug‑09          2,722           2,032.00          74.65%            

 

  July‑09          3,285           2,370.00           72.15%

 

 

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)

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?

maybe it will help???

http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=10542 - http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=10542

 

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

I will try to assit if needed