CrossTab Last4WeeksToSun 1

[themich]

How do I utilize the Last4WeeksToSun formula in a cross tab?

I want the "pay_ending_period" field to be limited to last four weeks (actually I wanted last five weeks - but I'll settle for four). I found the Last4WeeksToSun formula and attempted to use it: cross tab expert > column>group options>options>use a formula as group name and entered the following.



{current_prt_check_deduct.period_end_date} >= Last4WeeksToSun

Error says formula must result in a string.

Any advice would be greatly appreciated.

 

[ianoctdec]

Try:

{current_prt_check_deduct.period_end_date} in Last4WeeksToSun

Dana

 

[lbass]

I'm guessing you want the crosstab to break out columns by week, so instead, you should be using the last4weekstosun in the record selection formula, and then adding the date field to the crosstab->group options->print on change of week.

It also appears that your date field might be of string datatype, so you would need to convert it. Try:

cdate({current_prt_check_deduct.period_end_date}) in last4weekstosun

If you want to use the last 5 weeks, just use:
cdate({current_prt_check_deduct.period_end_date}) in currentdate-34 to currentdate

If the cdate conversion doesn't work, show samples of your string date, identifying the format as dd/MM/yyyy or MM/dd/yyyy or whatever it is.

-LB

 

[themich]

Thank you Ibass!
I'm making the transition from Access to CR and it's a bit of a learning curve.
You were correct, I was placing the formula in the wrong area of the report.
I wound up using:


If {CURRENT_PRT_CHECK_DEDUCT.Period_End_Date} in MonthToDate
Then
{CURRENT_PRT_CHECK_DEDUCT.Amount}
Else
0

Which is a step in the right direction. Now I need to change the end of the if statement so that the amount doesn't show at all (instead of the zero) but atleast I have a jumping off point. Thanks again.

 

[lbass]

You can right click on the formula->format number->customize->suppress if zero.

-LB