Date Ranges

[ganjass]

Hi ALL

I need to create formulae for a manual cross tab, where if the months are any month apart from january then its a full month, however if its january it has to be the 1st to the 14th, any ideas how i would do this?

thanks in advance

 

[lbass]

Do you have the option of removing January 15 to 31 records from the report? You could use a record selection formula of:

not(day({table.date}) in 15 to 31 and
month({table.date}) = 1)

You haven't described your crosstab, but if the columns are the months, if you cannot select out the undesirable records, then you could add the above clause to your column formulas, as in:

//{@month-1}
if not(day({table.date}) in 15 to 31 and
month({table.date}) = 1) and
{table.date} in dateadd("m",-1,{?date}-day({?date})+1) to
{?date}-day({?date}) then {table.amt}

...where {?date} is a discrete date parameter and you want to evaluate the months prior to this date.

-LB

 

[ganjass]

Hi Lbass, the refined spec which i now have is:

the columns are the months which are based on date diffs as so Month2:

if Datediff("m",{@Placement Month Start},{debt_trans.tx-date}) <= 1 and
{debt_trans.tran-code} startswith ["DR1","DR3","DR4","DR5","DR309"] then {debt_trans.tx-amount}

@Placement Month Start:
Date(Year({debt.dt-datinstr}),Month({debt.dt-datinstr}),1)

month 1 would be dates from 1/1 to 14/2 the diff2 would be 15/2 to 14/3 and 15/3 to 14/4

thanks for your help

 

[synapsevampire]

Your formula has nothing to do with January.

More important than posting text descriptions and attempted formulas would be to provide a simple spec:

Crystal version
Database
Example data
Expected output

It looks like you're trying to do a monthly 15th to 15th here, which since you're using a manual crosstab, so you might just hardcode in each months range values into 12 Running Total formulas using the Evaluate->Use a Formula.