Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum (If ( Min( - Error in Expression

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

2 Replies
swuehl
MVP
MVP

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
Not applicable
Author

Hi,

Thank you - that helped

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

br

Nils