Same dimension and expression, but different results

[hfkchristian]

Hi again

I have two straight tables with the same dimension and expression, but the result of the expression is different in the two tables.

In one table I have just one dimension, InvoiceNumber, and one expression, Sum(InvoiceCount), and it gives the result of 1, which is correct.

In another table I have the same dimension and expression, but also some other expressions. There are some disabled dimensions and expressions too. In this table I get a result of 2, which is wrong. (2 may be the number of lines on this invoice?)

To test it more, I put the expression in a text box, and the result was 1.

I don't understand how the same dimension and expression, both in straight tables, can give different results. Are there any settings or data model errors I could look at to try to fix this?

InvoiceNumber and InvoiceCount are in different data model tables, but they are linked with the field InvoiceId.

[marcus_sommer]

It seems that the used dimensions within the second table aren't fitting properly to eachother so that the virtual-table behind the straight-table creates a kind of cartesian product and therefore the measures on it return an unexpected or wrong result. Maybe it's enough to remove these dimensions - otherwise you will need to rethink your datamodel.

- Marcus

[hfkchristian]

I tested it a bit more, and I found that the result of the expression is wrong if there is an AGGR expression in the same table. If I remove all AGGR expressions, I get the correct answer of 1. If there are one or more AGGR expressions in the table, the result will be wrong. (The number of lines in the table stay the same)

I don't understand this. How can the result of one expression change depending on other expressions..?

[marcus_sommer]

Where did you use these aggr-expressions in dimensions and/or expressions and how does they look like?

- Marcus

[hfkchristian]

I just found that it has nothing to do with the aggr-expressions after all. Instead, the problem occurs when the field InvoiceId is used in another expression (table expressions) in the same table. But I am still confused why this happens.

[hfkchristian]

These are the two data model tables, with the relevant fields. InvoiceId is distinct in the Invoice count table, and InvoiceCount always has the value of 1. InvoiceId is not distinct in the Invoice table.

Invoice table

InvoiceId

InvoiceNumber

Invoice count table

InvoiceId

InvoiceCount

[marcus_sommer · Accepted solution]

It happens because of the relationship between your associated tables and it's not enough to associate the tables with one or maybe several fields as (combined) key else you need to consider the relationship of 1:1 or 1:n or even n:n.

I think there is no general way how to solve such task because it will depend on the further requirements to the datamodel and the needed views within the UI.

In your case I think you could replace:

Sum(InvoiceCount)

with

count(distinct InvoiceId)

If it's don't work or it's not applicable for some reasons you will need some changes in the datamodel which might be to duplicate the InvoiceId as InvoiceIdCount within the "Invoice count table" and then using this field as dimension in the charts and/or the aggr.

- Marcus

[hfkchristian]

I actually had count(distinct InvoiceId) before, but the calculations were so slow, and I tried to speed it up by using a sum calcuation instead. I don't think I can go back to count distinct. We have a lot of data, and it's just too slow. I'll see if I can find another way to do this. Thank you for your help and explenation.

[marcus_sommer]

Yes, you might need to rethink your whole datamodel if there are such serious performance issues. Beside of this should the following result in a better performance for your counting of the InvoiceId:

Invoice:
load * from Invoice;
   left join(Invoice)
load InvoiceId, 1 / count(InvoiceId) as InvoiceCount resident Invoice group by InvoiceID;

and your expression will be like before:

Sum(InvoiceCount)

- Marcus

[hfkchristian]

This worked. Thanks a lot 🙂