Sum of calculations




Hello folks,

I have a dynamic table that includes a Qty field and a ListPrice field. On the same row I have a total using Qty * ListPrice

Now, I also have a running total at the bottom which looks something like this:

Sum(ProductRow1[*].RowTotal) + Sum(ProductRow2[*].RowTotal) + Sum(ProductRow3[*].RowTotal)

Now here is my problem, I also have a "running total" for all the ListPrice events at the bottom of the ListPrice column, and this works fine, and it looks like this:

Sum(ProductRow1[*].ListPrice) + Sum(ProductRow2[*].ListPrice) + Sum(ProductRow3[*].ListPrice)

It works fine, except I want this "ListPriceTotal" only to include items that have a Qty attached. It right now is populated via XML with the ListPrice dynamically populated. I don't have room to make another column to "Hide" this total, I would like to use FormCalc.

I have tried the following without any luck, as it only totals the first row from each section and not the rows following within the same section:

Sum (ProductRow1[*].ListPrice * ProductRow1[*].Qty)

Basically here is what I would like to see happen....

Item# Qty ListPrice DicsountPrice RowTotal

ABCD 2 $500 $400 $800

WXYZ 3 $250 $200 $600

SubTotal $1400

I want to see the ListPriceTotal field to show $750 in the above scenario.

If item # WXYZ quantity = 0 then the ListPriceTotal field would show $500





Unless you want to keep a separate "hidden" column (it could be 1 charachter wide and not available to the user) you would have to to a pretty sophisticated expression to get what you want.