Wrong total amount

[#1]

Hi,

I have some problems solving an issue maybe you can give me an idea.

I have to display total spends on different dimensions. And the total amount should be the same on all the dimensions.

I have 44 dimensions, and the total amount should be the same for each of it.

The issue is that the amount is not the same for every dimension.

On 15 dimension the amount is wrong, is higher significantly. And on others 29 the amount is correct.

The expression that is calculating the total amount is "=sum(Invoices)". If I select Expression Total option on every dimension. The amount is the same for all, but if I select Sum of rows, the amount is wrong for 15 dimension and for the rest is fine. This issue I discover it when I exported some dimensions in excel, and the sum was different, equal to the result of "sum of rows" from qlikview.

Can you give me an idea for this ?

[jmmayoral3 · Accepted solution]

This is happening because you have dimensions with duplicated information.

You are speaking about invoices. Usually Data base systems working with invoices has two tables (or more): invoice headers and invoice details.

Invoice headers has the total amount of the invoice.

Invoice details has the amout of each row of the invoice  and, usually, the total amount of the invoice to avoid to do calculations. If you don´t have this, you have to add amounts row by row of the invoice to know the total amount.

Here could be the problem. If you have this situation, when you do sum(invoice) and headers table has more than one rows on details table, yor are doing a cartesian product and you show wrong information.

Look at this example and pay attention to image:

I create two tables: invoice headers and invoice details joined on invoice_id field.

Image shows a table box with al fields of both tables.

Just below, two tables using 'invoice_id' as dimension and sum(amount) as expresion. Second table does a cartesian product when I show row_id too. You can see that the total is good (40000) but if you sum all rows, it would be 50000.

And just below, (on the last row), other two tables using 'invoice_id as dimension and sum(invoice_amount)  as expresion. You can see that total amount would be 40000 but 50000 is being showing.

Headers:

Load * inline [

invoice_id, amount

1,          10000

2,          30000

];

details:

Load * inline [

invoice_id, row_id, invoice_amount , row_amount

1,          1,      10000,           5000

1,          2,      10000,           5000

2,          1,      30000,           30000

];

[#3]

Thank you for your answer!
Very helpful!