I am using this filter in a GV report:

and({Trx Type} = 1,{Job}<>100,or(and({Trx Date} >= {Start Date},{Trx Date} <= {As of Date}),and({Trx Date} >= {LY Start Date},{Trx Date} <= {LY As of Date})))

The filter evaluates okay but the problem is the {Job}<>100. It doesn't do anything to the View even though it's supposed to omit Job # 100.

Instead of {Job}<>100 use trim({Job})<>"100"

The Job number is actually a text field and needs to have quotes around it.

Steve

This worked. Thanks.

My second question is: I am using this formula to calculate the variance between two columns (column E - column F).

=IF(OR(E10=0,F10=0),100,E10/F10)

I want my variance to show as a percentage and if I use this formula it works fine if column F is zero because then it shows 100, but if column E is zero I get an error even though it's supposed to show -100.

Hi Miriam,

Is this in the Financial Reporter or GridView?

In either case, you must test to see whether F10 = 0, since division by zero is undefined.

This is a GV report linked to Excel. With an XDView link it automatically updates Excel with the formula stored in Excel.

When I divide column E by column F I want the variance to show as a percentage. In the case where column F is 0 the formula works, but if E is 0 then I have an error and if both columns are 0 then it shows 100% where as it should show 0.

Are you getting the error in GridView or Excel? What EXACT error are you getting?

Please let us know what results you want for each of the following cases:

E10=0 and F10=0 show: 0%
E10=0 and F10=2 show: -100%
E10=1 and F10=0 show: 100%
E10=1 and F10=2 show: -50%

I was getting the #DIV/0 error.

Why -50%?

Because it decreased by 50%. For example if I have 50 in column E and 100 in column F then it means that the 100 decreased to 50 which is a variance of -50% (so whenever column F has a higher value than column E the variance will be negative).

Use:

This will give you the percentage increase (decrease) between the two numbers.