Date Range issue

[frink333]

We produce a stock sheet which has our stock codes as groups, and has formulas which summerise the stock delivered over a 12 week period on a weekly basis ie "aud.dat in (currentdate) to (currentdate -14)"
"aud.dat in (currentdate -15) to (currentdate -28)"
etc.

There are tweleve weekly columns per code group.
I now need to change these columns to half monthly columns (1st to 15th and 16th onwards per month for a three month period going backwards).

I am using version 8.5, and the aud table is SQL.

Any ideas will help!
Thanks.

 

[lbass]

I think you could use formulas like:

//{@currentperiod}:
if day(currentdate) <= 15 then
(if {table.date} in date(year(currentdate), month(currentdate), 01) to currentdate then {table.amt}) else
if day(currentdate) > 15 then
(if {table.date} in date(year(currentdate), month(currentdate), 16) to currentdate then {table.amt})

//{@lasthalflastmo}:
if {table.date} in date(year(currentdate), month(currentdate)-1, 16) to date(year(currentdate),month(currentdate),01)-1 then {table.amt}

//{@firsthalflastmo}:
if {table.date} in date(year(currentdate), month(currentdate)-1, 01) to date(year(currentdate),month(currentdate)-1,15) then {table.amt}

//{@lasthalftwomosago}:
if {table.date} in date(year(currentdate), month(currentdate)-2, 16) to date(year(currentdate),month(currentdate)-1,01)-1 then {table.amt}

//{@firsthalftwomosago}:
if {table.date} in date(year(currentdate), month(currentdate)-2, 01) to date(year(currentdate),month(currentdate)-2,15) then {table.amt}

-LB