Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Hi,
Thank you - that helped
I ended up with: sum( aggr ( sum( if ( CreateDate = InvoiceDate, Amount)), InvoiceDate))
br
Nils