Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Miguel_Angel_Baeyens

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
Author

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
Author

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

Not applicable
Author

Miguel and Nilsberg,

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