Doing sums with a column formula

HCL Domino Domino Forum
1

We have a bill book Notes app which contains “bill” and “feebudget” documents. There is one feebudget doc per client and 0 to any number of bills.

I am trying to create a view by client that shows whether we are ahead of or behind budget.

It is categorized by client code.

I have a column for bills to date (formula = BBTOTAL) which is totalled.

I have a column for budget, also totalled, with formula:

mth=@Month(@Now);

@If(mth=4;FBapr;mth=5;FBmay;mth=6;FBjun;mth=7;FBjul;mth=8;FBaug;mth=9;FBsep;mth=10;FBoct;mth=11;FBnov;mth=12;FBdec;mth=1;FBjan;mth=2;FBfeb;FBmar)

These work just fine - I can see total bills and budget to date by client! so what is the problem?

I need to subtract, effectively at total level, one from the other to get the under/over budget. So I created another totalled column with formula:

mth=@Month(@Now);

budg=@If(mth=4;FBapr;mth=5;FBmay;mth=6;FBjun;mth=7;FBjul;mth=8;FBaug;mth=9;FBsep;mth=10;FBoct;mth=11;FBnov;mth=12;FBdec;mth=1;FBjan;mth=2;FBfeb;FBmar);

@If(@IsAvailable(BBTOTAL);BBTOTAL;0)-@If(form=“Feebudget”;budg;0)

To my mind, at document level, this should pull out the bill value BBTOTAL if the doc is a bill (and it does) and should pull out the budget value if the doc is a budget (it doesn’t), and thus effectively create the difference at column-total level. It fails with “incorrect data type for operator or @function” on every feebudget doc… yet the budget formula in its own column works ok!

???

what am I doing wrong?

2

Subject: Doing sums with a column formula

Because of the datatype error, I would bet that you have fields containing non-numeric data. Could the budget be stored as text? First check the properties of those fields in several docs (from the view, not by opening the doc!). Then, temporarily create a simpler column and add some error handling using @Isnumber or @IsError. That should pin-point the problem.

If the budget value is in one document and the individual bills are separate, you can adjust your formulas a bit in order to show the bills as negative amounts and actually show a total per category.

3

Subject: RE: Doing sums with a column formula

nooooooo - its worse than that - its just a typo !

mth=

or mth:=

aaaagh

anyway this works:

mth:=@Month(@Now);

@If(@IsAvailable(FBapr);-@If(mth=4;FBapr;mth=5;FBmay;mth=6;FBjun;mth=7;FBjul;mth=8;FBaug;mth=9;FBsep;mth=10;FBoct;mth=11;FBnov;mth=12;FBdec;mth=1;FBjan;mth=2;FBfeb;FBmar);@If(@IsAvailable(BBTOTAL);BBTOTAL;0))

I thought the same about non-numeric data, but they were all numeric.