Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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
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
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

Contributor III
Contributor III

Hi,

Thank you - that helped

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

br

Nils