Skip to Content
0

Effective use of counters in a formula.

Aug 02, 2017 at 12:10 AM

25

avatar image
Former Member

I have had trouble with counters in tallying various counts. Where I work there is basic business logic that an sales order, contains many codes. This system uses codes to describe all parts of what went into this order, from phone or web chat to shipping it. there is one table that holds orders and codes and sales persons.

Case. You have a table with sales persons and various codes for one order. these codes represent tasks which is part of the order. If the sales person has code 001, and BCS and WOC, we only want to tally the WOC.
If has BCS & 001 then would tally the 001. the logic is if there are only 2 codes (always an order will have an 001)
then tally the 001.
How would you do this in a formula?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Ian Waterman Aug 02, 2017 at 02:04 PM
0

Group data by Salesperson.

Create three formula

@WOC

If Transcode = 'WOC' then 1 else 0

@Other

If Transcode = '001' then 1 else 0

place this formula In salesperson group footer, you do not need to place the others on to the report layout.

@SPTotal

If Sum(@WOC, salesperson) >0 then Sum(@WOC, salesperson) else Sum(@001, salesperson)

Not as difficult as I first thought. If you want to then sum these as a report total you will need to use a variable.

Ian

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Aug 02, 2017 at 08:12 AM
0

Depends on your data structure

Are the codes 001, and BCS and WOC in a single field and thus there are multiple records per order, or is there a single record with multiple Code fields.

I suspect the former, in which case it would be easier to do this in a command, can you write SQL?

If not then you will have to create formula based on group counts, please show a sample of your data structure

Ian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

The structure is like this.

order# Salesperson TransCode

12345 101 001

12345 101 BCS

12345 101 WOC

12345 99 CQU

Suppose for example we only want to count the BCS if there is NO WOC per that order#

0