Create unique quarters

[katididdy]

Our fiscal year runs from April 1st to March 31st. I am trying to create a report that is divided by quarters. In each quarter I need to have the number of overtime hours worked by a company and the dollar amount paid. I am pulling my data from JD Edwards. Any help is greatly appreciated!

 

[synapsevampire]

Group the report by the year.

Build out a quarter formul:

numbervar Q1OTHours;
if {table.field} in {cdate(year({table.field}),4,1) to
{cdate(year({table.field}),6,30) then
"Q1"
else
if {table.field} in {cdate(year({table.field}),7,1) to
{cdate(year({table.field}),9,30) then
"Q2"
if {table.field} in {cdate(year({table.field}),10,1) to
{cdate(year({table.field}),12,31) then
"Q3"
if {table.field} in {cdate(year({table.field}),1,1) to
{cdate(year({table.field}),3,33) then
"Q4"

The Group by this formula.

Now you can do conventional summariesin the details by right clicking a field and selectinginsert->summary->sumorwhatever aggregate you need.

-k

 

[synapsevampire]

Ooops, typo there, change:

if {table.field} in {cdate(year({table.field}),1,1) to
{cdate(year({table.field}),3,31) then
"Q4"


-k

 

[fsreport]

hi
try this
Quater function

If (DatePart("q", {DATE_FIELD})) =1 then "QT1"
else
If (DatePart("q", {DATE_FIELD})) = 2 then "QT2"
else
If (DatePart("q", {DATE_FIELD})) =3 then "QT3"
else
If (DatePart("q", {DATE_FIELD})) =4 then "QT4"


fsreport

 

[synapsevampire]

fsreprot: Won't work, they have special quarters, reread the post.

-k

 

[katididdy]

Thank you for all the input! I was able to do what I was looking for in a cross-tab, and completed it a little while ago. This is the formula I created, called quarter, that made it work:

if {@monthname} = ["April", "May", "June"]
then 1
else
if {@monthname} = ["July", "August", "September"]
then 2
else if {@monthname} = ["October", "November", "December"]
then 3
else if {@monthname} = ["January", "February", "March"]
then 4
else 0;

I made "quarter" a column in the cross tab, and had the sum $ amount and sum hours as rows. After taking the work date as a month and converting that to a month name (hence {@monthname}), I had a neat report. So far, no errors. Thank you for replying!