Need to select last 4 quarters from current quarter

[aurunner611]

using CR 11, Querying Accpac 5.4 database.

I'm trying to write a formula that will select the last 4 quarters from the current quarter or date.

This is located in the record selection formula and the end result will display qtys of items sold in the last 4 quarters.

original formula i'm trying to edit to do this is...

{OESHHD.YR} = {?Year} and
{OESHHD.ITEM} in {?From Item} to {?To Item}

this currently asks for the year you want to display and the item number(s) you want to display.

i've tried to write a formula that will show last 4 quarters but keep getting errors and nothing i've searched has helped.

thanks in advance.

 

[raja2008]

Hello!

iam assuming that you are looking for the last four quarters not including the current quarter from Currentdate.

I created these formula's assuming that you are pulling the data of last four quarters into the report.

create two foumulas:
lastdayofpreviousquarter:
date(dateadd("d",-1,date(year(currentdate),(datepart("q",currentdate)*3-2),01)))

firstdayoflastfourthquarter:
date(dateadd("d",+1,date(dateadd("yyyy",-1,{@Lastdayof previousquaretr}))))

as you guesses by the names the formulas get the information.

Now use these in the selection criteria:
({recorddate} >= {@Fisrtdayoflastfourthquarter}) and
({recorddate} <= {@Lastdayof previousquaretr})

this will pull only the last four quarters data into the report.

Hope this helps.

 

[aurunner611]

thanks raja2008 for the quick reply,

i am getting an error when i go to save the record selection formula. Formula below

({OESHHD.AUDTDATE} >= {@firstdayoflastfourthquarter}) and
({OESHHD.AUDTDATE} <= {@lastdayofpreviousquarter}) and
{OESHHD.ITEM} in {?From Item} to {?To Item}

error on {@firstdayoflastfourthquarter} "a number is required here".

i'm assuming i will get the same error on the {@lastdayofpreviousquarter} when it gets there.

do i need to format the date of OESHHD.AUDTDATE?

 

[raja2008]

what kind of datatypes are the auddate.
and where exactly is it pointing the error is.
i tested the formulas and select criteria myself and it went fine.

check the datatype and change it to date if that's causing the problem.

i guess the formulas passed.

 

[aurunner611]

yeah i think the formulas are fine.

when i go into the pervasive DB control center it says audtdate is a Decimal

so i'm pretty sure i need to convert that to a date CR can understand since thats what it is looking for.

 

[lbass]

You need to show samples of how your decimal field displays and be sure to explain what date it corresponds to, i.e., also identify whether you are using MM/dd/yyyy or what.

-LB

 

[aurunner611]

I formatted the date easily by creating a new formula and using pwformatdate. end result is
pwFormatDate ({OESHHD.AUDTDATE})

but i have a new problem that i cannot figure out.
i have four formulas that figure out what period the results are in, which then figures which qtr they are in. In the details section of my report they get added and that produces what we've sold total for each quarter.
here is a sample of one of those 4 formulas.
formula name: 1stqtr
if ({OESHHD.PERIOD} = 1.0 or{OESHHD.PERIOD} = 2.0 or{OESHHD.PERIOD} = 3.0) then {OESHHD.QTYSOLD}
else 0

here is the problem. since we are in the 2nd qtr of 2008 my last 4 qtrs are going to be 2008 qtr1, and the last 3 qtrs of 2007 but somehow it is adding the 1st qtr of 2007 to 2008 and i'm getting the wrong data because of that.

i cannot figure out why it is pulling 1st qtr 2007...

 

[lbass]

That suggests that your record selection formula is still not correct. Please post it.

-LB

 

[aurunner611]

record selection formula:

({@fmtdate} >= {@firstdayoflastfourthquarter}) and
({@fmtdate} <= {@lastdayofpreviousquarter}) and
{OESHHD.ITEM} in {?From Item} to {?To Item}

and the corresponding formulas (also posted above from raja)

firstdayoflastfourthquarter:
date(dateadd("d",+1,date(dateadd("yyyy",-1,{@Lastdayofpreviousquarter}))))



lastdayofpreviousquarter:
date(dateadd("d",-1,date(year(currentdate),(datepart("q",currentdate)*3-2),01)))

 

[lbass]

Can you please show a sample of both so we can see what the function does?

OESHHD.AUDTDATE pwFormatDate({OESHHD.AUDTDATE})

-LB

 

[aurunner611]

oeshhd.audtdate = 20,071,211.00

pwFormatDate({OESHHD.AUDTDATE})= 12/11/2007


is that what you are needing?

 

[lbass]

I think the formulas are correct, so I don't see how you could be getting any records for prior to {@firstdayoflastfourthquarter}. My only thought was that somehow the formatting function wasn't working properly, but I can't really tell whether your original date should be read as yyyyMMdd or yyyyddMM or whether the formatted date is MMddyyyy or ddMMyyyy, but anyway, if that were the problem, then you'd likely get an error message about the month being greater than 12.

-LB

 

[aurunner611]

thanks lbass,

i'm not an everyday programmer and this was throwing me. so looks like it's a little deeper than what i thought.

 

[aurunner611]

ok,
the formula's raja gave are correct. here is what i'm running into

the pwformatdate function is formating the date into dd/mm/yyyy and i need mm/dd/yyyy

i've tried a couple of solutions but keep getting errors

i can't use totext because then i would have to convert everything. but when i try to create a new formula to convert @fmtdate it says "error date-time required here". my bad i thought it was already in date-time format.

does anybody know an easy way of correcting this problem?
thanks again

 

[lbass]

Try the following, which assumes that your field is always 8 digits long:

stringvar x := totext({OESHHD.AUDTDATE},0,"");
cdate(val(left(x,4)),val(right(x,2)),val(mid(x,5,2)));

-LB