Help with Crystal formula

[boboleft]

I have my report set up to group by accout#, all the details have been suppressed and had the summary total added at the group footer (acct#). A date parameter has been added to pull only the data I want in the selected date range. I want to be able to output Month to date total and year to date total at the acct# level. However, the date range will only work for one total. How can you generate the output listed below in Crystal?? I have tried cross tab but it was adding total for the customer name, address that I don’t want it. Does anybody know a better solution for this??

Acct# custname address MonthToDateTotal YearToDateTotal
123 ABC 123 main st 1500 2000

 

[IanWaterman]

You need to bring back data for year to date.

For Month todate use conditional formula

@MTD
If {yourdatefield} = {daterangeParam} then measurefield else 0.

You can then sum this, if you do not want to see previous months then in section expert suppress date using
{yourdatefield} <> {daterangeParam}

Ian

 

[boboleft]

Thanks so much. It worked perfect.

 

[boboleft]

Question,

I have hard coded the {daterangeparam} into the @MTD formula. How can I add the actual parameter into this formula, so i don't have to manually change the date every week??

Here is what i have set up on my report

@MTD: if (post.dt) in datetime (2010,01,01,00,00,00) to (2010,04,23,00,00,00) then {balance} else 0.

Parameter name: date range (pulling the post date} and I have placed on the report header. The parameter box will prompt for me to enter the start date and end date when i run the report.

Start date formula: Minimum ({?date range})

End date formula: Maximum ({?date range})

 

[lbass]

if (post.dt) in currentdate-day(currentdate)+1 to currentdate then
{balance}

This assumes that the current month falls within the daterange selected for the report.

-LB

 

[boboleft]

Hi LB,

Thanks for the formula. I have tried but it didn't quite work. Not sure if i have did it right. I have placed my @mtd formula to the group footer (acct#) when i put in my date parameter for 4/1/2010 to 4/23/2010. My @MTD only capture the last balance (4/12) instead of adding all the balance for april. I also have @YTD formula to add up the
@MTD total. Really don't know how to make this working anymore. Can you help??

4/1 500
4/7 1000
4/12 2500

 

[lbass]

The formula should be placed in the detail section. Then right click on it and insert a summary on it at the group level. I'm unsure whether these should be summed, but try that.

-LB

 

[boboleft]

Hi LB,

Yes, it worked, i have placed in the detail section and did a summary of the @mtd and placed at my group footer.

Can I ask for one more help on the YTD formula??

My YTD is not captures the Year to date balance anymore, how can I change my formula to also capture year to date balance on the same report??

thanks

 

[lbass]

For YTD, just create another formula like this first, place it in the detail section, and again insert a sum on it:

if (post.dt) in YearToDate then
{balance}

-LB

 

[boboleft]

Thanks so much with your help.