topic Re: Sum (If ( Min( - Error in Expression in QlikView

Sum (If ( Min( - Error in Expression

Anonymous — Wed, 08 Jul 2015 23:15:09 GMT

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 — Wed, 08 Jul 2015 23:42:11 GMT

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 — Thu, 09 Jul 2015 04:22:11 GMT

Hi,

Thank you - that helped

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

Nils