Need help in formula for month,YTD in crystal

[Former Member]

Hi all,

I have to create a crystal report where I have 1 amt field and 1 date field (2011.APR)format.In report I hv to create a parameter

(stringdatatype) (2011.Apr,2010 aug..) ex:Lovs in parameter. record has to get data according to month and year selection . for example if I pass 2011.Apr.as my parameter,

in month column need to get data from April 1st 2011 to April 30th 2011(that months data)

lastyearperiod column ...should get April 1st 2010 to April 30th 2010 data.

YTD : Jan 1st 2011 to april 30th 2011 data

Lastyear YTD: Jan 1st 2010 to April 30th 2010 data.

I have tried few formulas but nothing worked because I dont have trasaction dates .. datefield is 2011.JAN format. Need to use parameter to get data in period, lastyear perio, YTD , Last year YTD...Please help......I am new to crystal reports .

Thanks in advance,

Priyam

[Former Member · Accepted solution]

Assuming your Parameter is always entered as YYYY.Mmm eg 2011.Apr you can use split() to split parameter

into two parts.

You will need to check what is returned by Monthname for each month for example

Monthname(4, True) returns Apr. Your parameter must use same characters and case.

Year(datefield) = tonumber(split(yourparam, ".")[1]) and

Monthname(month(datefield),true) = split(yourparam, ".")[1]

Or if you want to be case insensitive

Year(datefield) = tonumber(split(yourparam, ".")[1]) and

uppercase(Monthname(month(datefield),true)) = uppercase(split(yourparam, ".")[1])

Ian

[Former Member · Accepted solution]

Hi Ian,

Thanks alot for your prompt response.

I have period and year datefields in database .I can use them as parameters.

Period: 1,2,3....12

Year: 2009,2010,2011.

I need a formula for MTD,Lastyear MTD, YTD, Lastyear YTD.Business fiscal year Jan 1st -Dec 31 st.

I am trying:

MTD

if {table.date} in date(year({?date}),month({?date}),01) to {?date} then {table.sales}

For YTD, try:

if {table.date} in date(year({?date}), 01, 01) to {?date} then {table.sales}

Now I need formulas using 2 parameters. It gives me A date is required here error.

Please help...

Thanks,

[Former Member · Accepted solution]

Priyam,

I truly believe that any database developer that stores dates as text for formatting purposes in the database, should be fired.

When they do it at the expense of also having a real date or datetime column they should have "DUMB A$$" branded on their forehead during their exit interview.

Date formatting is a function of the presentation or application layer, it should NEVER be done at the database level. And I'm not a huge fan of ALWAYS or NEVER...

Well my rant is over... As you can probably tell, you are the victim of a very poor database design decision.

Your biggest challenge is to get your dates out of that goofy date format and into something usable as a date.

This is probably the easiest method...

//Formula name: RealDate
Local NumberVar m := 
If SPLIT({TableName.TextDate}, ".")[2] = "JAN" THEN 1 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "FEB" THEN 2 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "MAR" THEN 3 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "APR" THEN 4 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "MAY" THEN 5 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "JUN" THEN 6 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "JUL" THEN 7 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "AUG" THEN 8 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "SEP" THEN 9 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "OCT" THEN 10 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "NOV" THEN 11 ELSE
IF SPLIT({TableName.TextDate}, ".")[2] = "DEC" THEN 12; 

DATESERIAL(ToNumber(SPLIT({TableName.TextDate}, ".")[1]), m, 1);

Notice that all of the dates are defaulting to the 1st of the month. This is because you aren't able to supply the day portion of the date.

Once you have that, you can start working on some date calculations...

{

//MTD
{@RealDate >= DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate), #1/1/1900#

//YTD
{@RealDate >= DateAdd("yyyy", DateDiff("yyyy", #1/1/1900#, CurrentDate), #1/1/1900#

HTH,

Jason

[Former Member · Accepted solution]

Thanks a lot Jason

Actually, I need to develope a GL report where user has option to choose datefield.

In database I have datefield as 2011.APR format.(YYYY.MMM)

So, If user enters 2011.APR then

period column: Has to get April 2011 amt

Last year Period: Has to get April 2010 amt

YTD: Has to get Jan 2011 - April 2011 data

Last Year YTD: Has to get Jan 2010 - April 2010 data

Formulas I have created:

For Period :

{@First date of the month}:Date(Year(currentdate),Month(currentdate),1)

{@FYend}: {?date}

Period: if cstr() in cstr({@firstdate of month}) to cstr({@FYend }) then {amt.vale} else 0 Last Year Period: @11: DateSerial(Year(currentDate()), Month(currentDate()), -364) if in cstr({@11})to cstr({@FYend })then {amt.value} else 0

YTD:

{@FYstart}:

if Month (CurrentDate) >= 1

then Date (Year(CurrentDate), 1, 1)

else Date (Year(CurrentDate) - 1, 1, 1)

if in cstr({@FYstart})to cstr({@FYend })then {amt.vale} else 0. Last Year YTD: {@LastYearstart} :DateSerial(Year(currentDate()), 1, -364) if in cstr({@LastYearStart})to cstr({@FYend })then {amt.vale} else 0

But YDT and Last Year YTD are giving me same amounts.Even Period and Last Year period is not giving me proper amts.

Can you please check my formulas and let me know where am I doing wrong.

[Former Member · Accepted solution]

Hello Priyam,

have you got answer to your question? I am facing the same challenge. Request you to share your formulas if you have already solved.

Thanks,