count forumla field

[jrhessey]

I have a report built with 2 groups, customer number and sales order number. I am trying to compare items sold and prices ytd, last ytd, and 2 years ago ytd. I created 3 formula fields called cyr, pyr, and 2yr.

cyr is this
if {SO_03SOHistoryHeader.OrderDate} = YearToDate then 1 else 0

pyr is this
if {SO_03SOHistoryHeader.OrderDate} in date(year(currentdate)-1,01, 01) to date(year(currentdate)-1,month(currentdate),day(currentdate)) then 2 else 0

2yr is this
if {SO_03SOHistoryHeader.OrderDate} in date(year(currentdate)-2,01, 01) to date(year(currentdate)-2,month(currentdate),day(currentdate)) then 2 else 0

I then created a field to only show item numbers that occur in each time period.

cyritem# is this
if {@cyr} = 1 then {SO_04SOHistoryDetail.ItemNumber}

That shows the item numbers on sales orders that were purchased ytd.

Now this is my problem, I want to count the total items per customer for cyr only. I used a regular count and it counts all item number for the customer over the 3 years. I tried to create a formula for counting...

cyrcount
if {@cyr}=1 then Count ({@cyritem#})

but that counts for the whole report.

I can't figure out what's going wrong... Any help would be greatly appreciated!

 

[lbass]

Try:

if {@cyr}=1 then 1

Then insert a sum (NOT a count). A count of a formula will always be the number of times the formula executed.

-LB

 

[jrhessey]

Excellent!! That worked perfectly! One more question then, your formula didn't work for counting the sales orders. I placed "@cyrsocount" in the group 2 section and summed it. I looked at the report, I only have 13 "1's" showing up, but it comes up with 116, the number of items on the sales orders. Why does it count the detail section when I put it in the group 2 section?

 

[lbass]

Please explain what you are trying to count. You might need to use a running total where you count on change of your group #2 field, or you might need to use a variable of some sort. Inserted summaries will count based on their incidence in the detail section.

-LB

 

[jrhessey]

First off, thanks for your help!! I really appreciate it! Here's what I have

so# item#
0 12340
12340 266
12340 268
12340 270

1 12345 (count this part)
12345 266 (exclude this part)
12345 370
12345 375

1 12347
12347 370
12347 375
12347 400

I want to count the groups of so#'s that appear for this year (cyr). The "1's" are from your forumla that you suggested, and "0" would not be included in the result. I figured it would only sum the group if I placed the field in the group 2 header but it summed the detail section. So I should end up with 2 orders, but using your formula in the group2 header, I end up with 6, the number of items. It does work however in not counting last years and 2 years ago. I tried a running total based on the change of my group 2, but still ended up with 116, not 13.

 

[lbass]

You haven't explained your group structure, so I'm not sure what group you should be using in the evaluation section. It looks like you should be doing a distinctcount of {table.so#}, on change of group (so#), and reset never if you want the count at the grand total level, or reset at a higher group level, if at the group level. The running total needs to be placed in the appropriate footer section (report for grand total, group for higher group level).

-LB

 

[jrhessey]

I apologize LB... Is this what you need??

Group 1 Header - Customer Number
Group 2 Header - Sales Order Number
Detail Section
Group 2 Footer - Sales Order Number
Group 1 Footer - Customer Number

Applying from the previous post

group 1 header
group 2 header - 12340
detail -
0 12340
12340 266
12340 268
12340 270
group 2 footer
group 2 header - 12345
1 12345 (count this part)
12345 266 (exclude this part)
12345 370
12345 375
group 2 footer
group 2 header - 12347
1 12347
12347 370
12347 375
12347 400
group 2 footer
group 1 footer

I'm trying to remember if I tried a runnging total of
distinctcount{SO_04SOHistoryHeader.SalesOrderNumber}. Everything I tried either returned the total for group 1 or group 2. I will try this first thing tommorrow. Thanks for your help Lbass!!

 

[jrhessey]

I don't think distinctcount{SO_04SOHistoryHeader.SalesOrderNumber} will work LB, that will give me the correct count for all orders for each customer, I need orders counted for each year also, just like the item numbers count. Something like this...

if cyr is = 1 then distinctcount{SO_04SOHistoryHeader.SalesOrderNumber}

cyr is the same field as in the first post also in the first post...

 

[lbass]

Sorry, I lost track of what you were counting. What you want is a running total that is a distinctcount of {SO_04SOHistoryHeader.SalesOrderNumber} and in the evaluation section, choose "use a formula":

{@cyr} = 1

Still don't know whether you want a total for the group#1, but if you do, then make the reset on change of group (customer number), and place the rt in the group #1 footer. Make a second running total with reset set to never for a grand total (report footer).

-LB

 

[jrhessey]

Hey! It's almost working!! I've got it counting correctly. I placed it in the footer of Group 2 and it works perfectly. However, is there any way I can get it to show the last number, which would be the total orders for that customer for that time period, in the Group 1 footer also?

 

[lbass]

If you place it in the group #2 footer, you will just see it accumulating a 1 for each group #2 that meets your criterion. Move this to the group #1 footer, and use a reset on change of group #1.

-LB

 

[jrhessey]

Holy crap, I could have sworn I tried that and it didn't work... oh well...

Woohoo!!! I think I've got everything I need now!! Thanks so much for your help LB!! It's greatly apprecited!!