using 2 expressions with there own dimension in one chart/table

[#1]

Good morning,

I have a problem with dimensions and expressions.

I have 2 tables with fields:

Complaints: contains one record for every complaint with a creation_date_year (2007, 2008 etc.).
Sales_Invoices: countains one record for every sales invoice with a creation_date_year (2007, 2008 etc.).

There is a connection between Complaint  - Sales_invoices but not always.

Now i want to make a barchart (and a Pivot table).

I need 2 expersions with each their own dimension.
I want to count the complaints (expression) and count them by creation_date_year complaint (dimension)
I want to sum the invoice amount (expression) and sum them by creation_date_year sales_invoice (dimension)

I can’t find a way to do this (give an expression their own dimension) in Qlikview. The only thing I have made is the barchart and pivot table with one dimension  (the creation date of the complaint). This is not accurate and some complaints have no link with sales_invoice and there for no year.

Can anyone help me with this situation?

Greetings,

Bart Kusters

[Anonymous]

Does your datamodel have one common date, used by both the complaints and invoices table. It would be easier to show counts and sums of the complaints and invoices against this date if you do, and they would then be independent of eachother (i.e. you wouldn't need to have had a complaint to find a corresponding invoice and vica versa).

You can achieve this by adding complaints and invoices into the same table, with the two dates being the same field.

Jonathan

[#3]

Thanks for your reply,

Unfortunately there is no common date. Complaints and invoices cannot be combined because you can make a complaing connecting to a sales_order or a company (in our ERP system).

[Anonymous]

You can make a commn date easily enough, using the creation_date_year fields from complaints and invoices.

Load your complaints into a table, the concatenate your invoices into the same table. Where there are common fields, such as creation_date_year, ComplaintNo, InvoiceNo, CustomerCode etc. call these the same thing. In this way, making a selection of one of these common fields will make QlikView return both complaints and invoices that are relevant. The measures, such as value or counts of the invoices and complaints, are not common fields.

Jonathan