Grouping and Formulas 2

[spiego]

I have a report that groups based on the following formula:

If year({Command.invdte}) in year(currentdate) - 2 to year(currentdate) -1 Then
    ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"")
else
   If year({Command.invdte}) in year(currentdate) - 1 to year(currentdate) Then
       ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"")

When I run the report, the group header display as:
2002/2003
2003/2004

For each group, I need to display the total commission for a month. I have placed a formula in the group header that looks like this:

If GroupName ({@Years}) = ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"") Then
Sum ({@Month_1_Top}, {@Years})
Else
If GroupName ({@Years}) = ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"") then
Sum ({@Month_1_Bottom}, {@Years})

When I run the report I only get numbers for 2002/2003. Why doesn't it sum for 2003/2004 also?

 

[lbass]

One problem is that you have overlapping data in the groups. Your first group instance includes data from 2003 as does your second group, and data can only appear in one group. If you want to use the same year in multiple calculations, you could eliminate the group and then use formulas like:

//{@2002/2003} to be put in the detail section:
If year({Command.invdte}) in year(currentdate) - 2 to year(currentdate) -1 Then {table.amount}

//{@2003/2004}:
If year({Command.invdte}) in year(currentdate) - 1 to year(currentdate) Then {table.amount}

Then you can right click on each of these formulas to get a sum for those particular years. I don't know how you are calculating monthly commissions if you are only looking at yearly data...

If you didn't intend for your calculations to include overlapping dates, then change the formulas to base them on dates rather than years only.

If you want further help, you need to share the contents of your month formulas.

-LB

 

[spiego]

Here are the month formulas:

1. @Month1_Top - To calculate the commission for the current month (Oct) for 2002/2003:

if month(currentdate) <> 1 and month(currentdate) = cdbl({Command.period}) and
    year({Command.invdte}) = year(currentdate) - 2 Then
        {Command.commission}
Else
if month(currentdate) = 1 and month(currentdate) = cdbl({Command.period}) and
    year({Command.invdte}) = year(currentdate) - 1 Then
        {Command.commission}

2. @Month1_Bottom - To calculate the commission for the current month (Oct) for 2003/2004:

if month(currentdate) <> 1 and month(currentdate) = cdbl({Command.period}) and
    year({Command.invdte}) = year(currentdate) - 1 Then
        {Command.commission}
Else
if month(currentdate) = 1 and month(currentdate) = cdbl({Command.period}) and
    year({Command.invdte}) = year(currentdate)  Then
        {Command.commission}

The report is a rolling month report. Meaning that when the report is ran now it will display Oct thru Sept as the column headings. Next month will be Nov thru Oct and so on.

 

[lbass]

Change your group formula {@years}to:

If {Command.invdte} in date(year(currentdate)-2,month(currentdate),01) to date(year(currentdate)-1,month(currentdate),01)-1 Then
ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"")
else
If {Command.invdte} in date(year(currentdate)-1,month(currentdate),01) to date(year(currentdate),month(currentdate),01)-1 Then
ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"")

What are the possible values of {command.period}? If these are month values related to when commissions are earned, then you should change your monthly formulas to:

//{@currentmonth}:
if month(currentdate) = cdbl({command.period}) then {command.commission}

//{@previousmonth}:
if month(dateadd("m",-1,currentdate)) = cdbl({command.period}) then {command.commission}

//{@twomonthsago}:
if month(dateadd("m",-2,currentdate)) = cdbl({command.period}) then {command.commission}

Repeat for remaining 9 months.

Then you would right click on these formulas and insert summaries (sums) at the group and grand total levels. You don't need to specify the years in the formulas since the grouping will take care of that. Nor do you need separate commission formulas for each year.

-LB

 

[spiego]

lbass,

I changed my group formula {@year} as you suggested. It displays fine. I left my formula that calculates the commission for Oct and for the most part, the numbers are fine. For those brokers that did not have the correct total, I did notice that if there was commission in the last week of Sept it was not included. According to our fiscal calendar, the last week of Sept falls under period 10 as well. The table that I am reporting off of has those transactions as period 10. I believe it has to do with the {@years} formula, but I am unsure what needs to be done.

 

[lbass]

Your formulas and mine assume that your periods correspond to months. If they don't, you need to explain why. I'm guessing that it has something to do with whether a week ends in the period. If you supply the rules that determine what period a week falls into, I'll try to help with the formulas.

-LB

 

[spiego]

lbass,

I had some other fires to put out last week, so I was not able to respond. Anyway, the company that I work for uses a 4-4-5 accounting structure. This means that the first and second period are 4 weeks long, and the third is 5 weeks long. The cylcle then starts over for period 4. I might have a way for this to work. I will try it out and let you know what happens.

 

[spiego]

lbass,

I am finally able to get back to this report. Anyway, you suggested the following code:

//{@currentmonth}:
if month(currentdate) = cdbl({command.period}) then {command.commission}

Does this get placed in the detail section of the report?

 

[lbass]

That is a detail formula, yes. But, you said in your last post that your firm uses 4-4-5 week accounting periods, so I don't think this approach would work. In other words, I can't see how the month value of a date would correspond to your accounting periods. FAQ767-1960 might help.

-LB

 

[spiego]

Thanks for the post. I will look it over.

 

[spiego]

LB,

I created another report and based it on your second post on 10/4. I just desinged it to do the month of November. When I run the report, the data is wrong in that it appears to be inclunding commission regardless of the year. For example, there's a broker who received commission in Nov. of 2002 and Nov. of 2003. I need it to only show the amount for 2002 for the group 2002/2003. The amount in 2003 should show up in the group 2003/2004. Here is what the report is to look like if I ran it today:

           Nov  Dec  Jan  Feb  Mar  Jun  Jul  Aug  Sep  Oct
Broker A

2002/2003  $100 225   0   300  300  280  340  200  500  0

2003/2004  $190 256   100 200  400  350  175  300  400  0

For 2002/2003 Nov & Dec are for 2002 and Jan thru Oct are for 2003.

For 2003/2004 Nove & Dec are for 2003 and Jan thru Oct are for 2004.

Depending on what month I am in when I run the report, that will be the first month going from left to right.

 

[lbass]

Please copy your {@year} formula into this post.

-LB

 

[spiego]

{@year} formula:

If {Command.invdte} in date(year(currentdate)-2,month(currentdate),01) to date(year(currentdate)-1,month(currentdate),01)-1 Then
    ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"")
else
If {Command.invdte} in date(year(currentdate)-1,month(currentdate),01) to date(year(currentdate),month(currentdate),01)-1 Then
       ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"")

 

[lbass]

The formula looks correct and I can't recreate your problem. Please place the date field in the details section next to the commission--I don't think it's possible that you could see November dates for two different years in the same group. Sort the date field and notice the range of dates for each date group.

-LB

 

[lbass]

PS. This might still be a problem of how your periods are set up as they compare to months. Try adding the command field to the details section, too, and then observe how the periods relate to the months.

-LB

 

[spiego]

LB,

I did as you suggested, and if I drill down on "2002/2003", I only see detail for those years as is the case for "2003/2004". However, I noticed that I am somehow getting an extra grouping that is blank but has numbers. Here is what it looks like:

          Nov  Dec  Jan  Feb  Mar  Jun  Jul  Aug  Sep  Oct
Broker A
          700

2002/2003 200

2003/2004 100

Is there somewhay in which I can save the data with the report and email it to you?

 

[lbass]

You have records outside of the range included in the year formula. This means your record selection formula does not match the years used in your group formula. Go to report->edit selection formula and add:

{Command.invdte} in date(year(currentdate)-2,month(currentdate),01) to date(year(currentdate),month(currentdate),01)-1

We have to keep this in the forum so that others can learn from it as well.

-LB

 

[spiego]

LB,

I was able to get rid of the records outside of the range. However, the month of November for the group 2002/2003 is still accumulating for both 2002 and 2003. Same with 2003/2004.

 

[spiego]

Regarding my previous post and after looking at the forumula you suggested:

//{@currentmonth}:
if month(currentdate) = cdbl({command.period}) then {command.commission}

Since the formula is only based on month, isn't that why it accumulates for for both 2002 and 2003 for November. How does it know to only do it for 2002 for the group 2002/2003?

 

[lbass]

No, that's not why. Since you are grouping on year, only months in those years will appear within a particular year group. As I said earlier, I think it has to do with the way your periods work--I don't think they match the months, and therefore they are not exactly corresponding to the years either. Why don't you define the command periods here using dates? Show the value of the command period and then show the corresponding dates that should be included in that command period.

-LB