Sum (If ( Min( - Error in Expression

[Anonymous]

Hi,

I trying to make a sum of a field if some conditions are OK, but keep getting "Error in expression".

I've also tried using Set Analysis with same result

Sum( if (CreateDate > Min(InvoiceDate), Amount))

CreateDate and Amount is in one table - InvoiceDate is in another table

Can anyone explain why this is wrong?

Best regards

Nils

[swuehl · Accepted solution]

Generally speaking, you can't use an aggregation function like Min() within another aggregation function like Sum().

You can probably use advanced aggregation, the aggr() function, to do achieve what you want, something like

=Sum( Aggr( If(CreateDate>Min(InvoiceDate), Amount), Dimension1, Dimension2))

Replace Dimension1, Dimension2 with appropriate field names (not necessarily 2, 1..n).

Think of it like

If(CreateDate>Min(InvoiceDate), Amount)

is an expression in a chart with dimensions Dimension1 and Dimension2.

And since I said, generally speaking, you can indeed use something like above, but with the addition of the TOTAL qualifier:

=Sum( if (CreateDate > Min( TOTAL InvoiceDate), Amount))

Can't tell if this will fulfill your requirements without knowing more details of your model and context of the expression.

[Anonymous]

Hi,

Thank you - that helped

I ended up with:     sum( aggr ( sum( if ( CreateDate = InvoiceDate, Amount)), InvoiceDate))

br

Nils