Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
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

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.

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.

Community Browser