Solved

# Generic Inquiry Calculated field style change.

• 2 replies
• 54 views

• Freshman I
• 4 replies

=IIf((Sum([PMCostBudget.CuryAmount]-[PMCostBudget.CuryActualAmount])<0),'green0')

Above expression is not working. This field is a calculated field to find the variance.

icon

Best answer by Eric Ratté 2 February 2024, 22:25

View original

### 2 replies

Userlevel 3
+1

Hi,

I attached an example.

To make it work, here are some tips.

1. When using the function SUM in an expression as a Data Field of the result grid:
1. You cannot set an aggregate function on the line of the result grid.
2. You have to add field at the Grouping tab
2. You cannot use the function SUM in the Style expression

In addition to, I see that the IIF function in your example is not complete. There are 3 segments split by commas =IIF(CONDITION,TRUE,FALSE). When the conditions are met, the TRUE segment will be displayed. Otherwise, the FALSE segment will be displayed.

=IIf((([PMCostBudget.CuryAmount]-[PMCostBudget.CuryActualAmount])<0),'green0','red0')

Userlevel 1

Thanks this helps.

I have a similar setup that i cant get to work, would love your thoughts as to whats wrong

=IIf(iif(year([ARInvoice.CreatedDateTime])=2020, [ARInvoice.CuryOrigDocAmt], 0)-iif(year([ARInvoice.CreatedDateTime])=2019, [ARInvoice.CuryOrigDocAmt], 0)>0, 'good', 'bad')

I actually want the SUM of [ARInvoice.CuryOrigDocAmt] for each one. In its current format it just seems to take the first [ARInvoice.CuryOrigDocAmt]. How should this be written?

The overall requirement of the generic enquiry is one, calculate the ales per year for a customer, (this section is working fine) and then two, colour the box depending on whether the customer has brought more or less than the year before.