| Author |
Message |
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
   Topic: DateAdd Function
Posted: 09 Jul 2010 at 2:29pm |
Hi there,
I have am trying to use the DateAdd function to extrapolate a date off a parameter field.
This works where I have one set of parameter fields which lets say represent a date range between 01/06/2009 and 31/05/2010 and one DateAdd formula with the DateAdd function in it.
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.
So it appears that you can only have one DateAdd formula per set of parameter fields? Can anyone confirm this and or a way around the problem.
Thanks,
KR
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jul 2010 at 2:54pm |
|
Can you explain further?
|
|
IP Logged |
|
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 09 Jul 2010 at 6:54pm |
Hey, Thanks for your interest it's really appreciated. I've tried detailing this as best as I can.
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.
Ok where the problem starts is when I create a second lot of date diff formula. These are as follow.
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.
When I am in the process of creating second running total formula and save it, it actually changes the formula in the first running total to reflect the formula I just save in the second running total???
|
|
IP Logged |
|
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 09 Jul 2010 at 7:09pm |
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jul 2010 at 2:39am |
I think you are over complicating things.
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
{DEL_DATE} in dateadd('yyyy',-1,{?Param Date}) to {?Param Date}
to get 12 full months would be eaiser to use dateserial function.
Does this help?
|
|
IP Logged |
|
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 10 Jul 2010 at 5:26pm |
Hi There,
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Jul 2010 at 4:04am |
Even though they come from different tables if you joined correctly you should be able to group.
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)
|
|
IP Logged |
|
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 14 Jul 2010 at 11:59pm |
Hi there,
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.
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
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Jul 2010 at 3:52am |
A couple of things to consider.
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...
|
|
IP Logged |
|
keithrichards
Newbie
Joined: 30 Jun 2010
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 15 Jul 2010 at 8:01am |
Hi there,
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%
|
|
IP Logged |
|
|
|
