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 ?
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.
Load * inline [
Load * inline [
invoice_id, row_id, invoice_amount , row_amount
1, 1, 10000, 5000
1, 2, 10000, 5000
2, 1, 30000, 30000
Then why on some dimensions I have the correct results (the sum of rows is the same with the total expression), and on other is different ? It should be the same on everyone of it.
ok so you have 1 expression, that has only a total so I do not understand when you say that you have different totals for the dimensions, do you apply any filter to say this or what else?
I have the expression =sum(invoices) that is on every dimension. So the total amount should be the same always.
For example: I select Division and the total amount is 19.298.890 (this is the correct result) and the Expression Total is selected. If I select Sum of Rows on this dimension the number is the same, maybe few decimals are added, but it's fine. But on Category the total amount is 19.298.890 (the same) on Expression total, if I change to Sum of Rows, the number changes to 19.723.450,24. And this is happening on more dimensions not only on Category. But there are dimensions on which the number is ok.