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 ;
Count ACCUM0 1 ;
TOTAL0 accum0;
Average ACCUM0 [value0] ;
ACCUM1 1 ;
TOTAL0 accum1 0= if “NA” ; then accum0 accum1 / ;
# 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

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

The summation of all the elements.
The sum of all positive items.
The second definition of average.
Synonym for AVG.
The first definition of AVG.
The count of all the rows.
The count of all non-blank values in the column.
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 - ;

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 34 other followers