Totals & Subtotals in FormLis

Totals are one of the coolest features in FormLis. When you make a form, you can add ‘views’ to show the submitted data, and these views can have totals (see:
Fullfilled Requests by Quarter in the Purchase Request Tracker, Time Spent Per Person in the Legal Time Tracker, and All Campaigns in the Marketing Manager Demo). For a while, FormLis let you define totals using an accumulator style. This was powerful, and power is good, but not at the cost of simplicity; I’m going to migrate to something like what I had before.

The current accumulator method lets you define accumulators using ACCUM?? (where ?? is any number) and you use its value in TOTAL?? words. If you cleverly combine accumulators you can create custom counts, averages, or sums.

The earlier method let you have standard sums, averages, and counts by using predefined words. You said SUM?? (where ?? is an existing column) and that column would have a sum total. AVG and COUNT were used the same way to give averages and counts.

Desired Total	Current Method	Former Method
Sum	ACCUM0 [value0] ; TOTAL0 accum0 ;	SUM0
Count	ACCUM0 1 ; TOTAL0 accum0;	COUNT0
Average	ACCUM0 [value0] ; ACCUM1 1 ; TOTAL0 accum1 0= if “NA” ; then accum0 accum1 / ;	AVG0
# non-blank	ACCUM0 null? if 0 ; then 1 ; TOTAL0 accum0 ;	Can’t be Done
“# Rows”	ACCUM0 1 ; TOTAL0 accum0 ” Rows” concat ;	Can’t be Done
# of >1000	ACCUM0 [value6] 1000> if 1 ; then 0 ; TOTAL0 accum0 ;	Can’t be Done
Sum, red if < 0	ACCUM0 [value6] ; TOTAL0 accum0 <0 if red then accum0;	Can’t be Done

The accumulator method is more flexible but only power users would understand it. Worse, it can’t handle averages the way users expect. Average has two meanings when categories are involved:

1. Average is the sum of all the values divided by the count. (as is done currently)
2. Average is the average of the subcategory averages treated with equal weight.

Category	Value
Alpha (Avg 100)	100
Beta (Avg 0)	0
	0
	0
	0

In the table above, the overall average is 20 by the first definition, 50 by the second. Users seem to prefer the second definition of average; which would be very hard to write in terms of accumulators, I don’t think I could do it.

2 Steps Back, 1 Step Forward

The new system is essentially the first method with some added capabilities; It will support

SUM

The summation of all the elements.

SUMP

The sum of all positive items.

AVG

The second definition of average.

AVGC

Synonym for AVG.

AVGD

The first definition of AVG.

COUNT

The count of all the rows.

COUNTNB

The count of all non-blank values in the column.

COUNTP

The count of all positive numbers in the column.

When you use one of these words, FormLis will display it for you in that column automatically. So its enough to just write SUM0 to show the sum for column 0. This default behavior will reuse the columns value printing code; so if you color negatives in the column, then the total will share that behavior. You can customize the behavior by writing a TOTAL method for the column, a NOOP (TOTAL0 ; ) will hide it.

More advanced behavior can be built using hidden columns. For example to SUM all values >= 4000, create a hidden column that stores (values – 4000) and take SUMP and COUNTP on that. Then compute the real sum by taking (sum + (count * 4000)):

COLUMN0 "My Values" ;
VALUE0 [value0] ; 
COLUMN1 hidden "calculations" ; 
VALUE1 [value0] 4000 - ;
SUMP1; COUNTP1 ; 

TOTAL0 sump1 countp1 4000 * + ; 

I’ll admit this is longer than accumulator style (ACCUM0 [value0] 4000 > if [value0] ; then 0 ; ). But I suspect most total calculations will be simple and this style makes those really easy.

About these ads

---