Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis - Aggregating rows by an ID, then show ID sums over $100K

Is there a way, using Set Analysis, to aggregate row amounts by an ID, then only show aggregation sums > $99k

Example:

Invoice # row Amount

Invoice Line 1 . 333 75,000
Invoice Line 2 333 25,000
Invoice Line 1 334 5,000
Invoice Line 2 334 1,000

Results: Invoice 333 100,000

4 Replies
MVP
MVP

Set Analysis - Aggregating rows by an ID, then show ID sums over $100K

Hello,

Assuming this data

Table1:LOAD * INLINE [Line, Invoice, AmountInvoice Line 1, 333, 75.000Invoice Line 2, 333, 25.000Invoice Line 1, 334, 5.000Invoice Line 2, 334, 1.000];


Create a chart object, Straight table, set Invoice as Dimension and the following as expression should work

If(Aggr(Sum(Amount), Invoice) >= 100000, Aggr(Sum(Amount), Invoice))


Hope this helps

Not applicable

Set Analysis - Aggregating rows by an ID, then show ID sums over $100K

Is there a specific reason you like to use set analysis? The following should give the result you specified.

Make a straight table using

Dimensions:

=subfield("Column1",' ',1)

[Invoice #]

Expression:

=if(Sum([row Amount]) >= 100000,sum([row Amount]))

Not applicable

Set Analysis - Aggregating rows by an ID, then show ID sums over $100K

Miguel,
that works, but it doesn't show totals of all invoices in the straight table or pivot table.

Highlighted
Not applicable

Set Analysis - Aggregating rows by an ID, then show ID sums over $100K

Miguel and Nilsberg,

Thanks for your answers.
I got my Set Analysis expression to work.