Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregate values in a cross table

Hello everyone,

I need your help about an aggregate in a cross table.

You an see in that example that I have several invoices numbers for only one order.agg.png

Therefore, all my lines are duplicated with the same content.

Do you know how can I aggregate the invoice number, to have in this example a result like that :

agg2.png

Thanks lot for your help

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

It is. Use aggr() in lieu of total <[Order Number]>  as follows in the formula of a calculated dimension

=aggr(  concat( Distinct    [Invoice Number] , ', ' ), [Order Number])

View solution in original post

7 Replies
gabriel_kirst
Creator III
Creator III

HI!

You can try using this on your expression:

=Concat(Distinct [Invoice Number],',')

There's attached an example using this.

Regards,

Gabriel

JonnyPoole
Employee
Employee

You can delimit the invoice numbers with the concat() function. Probably something like this

=concat( Distinct  total <[Order Number]>  [Invoice Number] , ', ' )   in an expression

This can be done in a chart expression, or you can build the data model this way in the load script.

Doing the latter will eliminate any duplicate records from the data model and you won't have to repeat any chart conditions when dealing with the duplicates elsewhere in the dashboard.

Otherwise you can do a NULL filter in the expression to get rid of the duplicates. Post the spreadsheet data and i'll post formula.

Not applicable
Author

Hello,

Thanks to both of you.

Your methods works fine with the concat as expression.

But it is possible to do this for a dimension ?

Thank you again !

JonnyPoole
Employee
Employee

It is. Use aggr() in lieu of total <[Order Number]>  as follows in the formula of a calculated dimension

=aggr(  concat( Distinct    [Invoice Number] , ', ' ), [Order Number])

Not applicable
Author

It works very fine.

Thanks lot !

Not applicable
Author

Thanks this document is useful !

geetaalhan
Creator
Creator

Hi,

You can also do aggregation at the script level.

Example:

Load Section,Order_Number,Order_Designation,Order_Supplier,Order_Date,Concat(Invoice_Number,',')

resident [table_name]

group by Section,Order_Number,Order_Designation,Order_Supplier,Order_Date;

Thanks ,

Geeta