Why Sum() is not correct at times?

[joefeyas]

In the case where I try to sum() columns not in my main fact table the sums frequently are wrong.

For example given 2 simple tables:

Table 1:    LineID, ItemID,  MiscData

Table 2:    ItemID, Color

LineID, ItemID,  MiscData

1, 1, xxx

2, 1, xxx

3, 1, xxx

4, 1, xxx

5, 2, xxx

6, 2, xxx

7, 2, xxx

8, 2, xxx

9, 2, xxx

10, 2, xxx

ItemID, Color

1, 'Blue'

2, 'Red'

Sum(If(Color='Red', 1, 0))

Clearly should be 6 but QV will return some other number less than 6.

Can anyone explain this behavior to me?

[#2]

Hi,

your sum return other number less than 6 because your script are not joined

This code:

Sum(If(Color='Red', 1, 0))

Return just 2 rows.

The join between tables returns 6.

try

TableA:
LOAD * INLINE [
    LineID, ItemID, MiscData
    1, 1, xxx
    2, 1, xxx
    3, 1, xxx
    4, 1, xxx
    5, 2, xxx
    6, 2, xxx
    7, 2, xxx
    8, 2, xxx
    9, 2, xxx
    10, 2, xxx
];

TableB:
INNER JOIN (TableA)
LOAD * INLINE [
    ItemID, Color
    1, Blue
    2, Red
];

[#3 · Accepted solution]

Your expression just counts the number of items colored Red in the Items table, not the number of lines with a red item in the Lines table. It has to do with you not using any attributes from the Lines table, so the Lines table is in fact not used.

If you would try Count( if(Color='Red', LineID) ) I suspect you have more chance of success.

[joefeyas]

Thank you, that worked perfectly!