Difference in results using set analysis and if statement

[#1]

Hi All,

I see different results if I calculate the same expression suing "Set analysis" an If condition"

For example:

IF condition =sum( If(Type='A',Hours))

Set analysis = sum({<Type={'A'}>} Hours)

Can someone tell me the reason for the difference in results using the above same..

Thanks in advance..

[swuehl]

How are your fields Hours and Type related in your data model? Are they located in the same table, or in different tables? If latter, what how are both tables related? Can you post some sample data?

[#3]

Both fields are located in two different tables. e.g. I have two tables

Transaction and Time, in Type table I have TypeID, Type and EmpName, and

in other table I have TypeID and Hours fields. So, both tables are joined

with TypeID.

[swuehl]

So your data model looks similar to this?

TIME:

LOAD TypeID, Hours INLINE [

TypeID, Hours

1, 8

2, 10

];

TYPE:

LOAD TypeID,Type, EmpName INLINE [

TypeID,Type,EmpName

1, A, EmpA

1, A, EmpB

2, B, EmpC

1, A, EmpC

2, B, EmpB

];

When you use set analysis, this is like selecting A in Type field, which renders only TypeID 1 as possible value.

So a sum(Hours) returns only the one record for TypeID = 1, which equals to 8.

When you are using the if() statement in your aggregation function, QV will evaluate record based, so QV needs to join the two tables:

TypeID,Type,EmpName, Hours

1, A, EmpA, 8

1, A, EmpB, 8

2, B, EmpC, 10

1, A, EmpC, 8

2, B, EmpB, 10

Then filtering Type = 'A' will return three records, so the sum of these three records returns 24.

Thus you might get different results using an if() statement in your aggregation compared to set analysis, depending on your data model.

[#5]

Yes, I got it. Thanks a lot...

[#6]

Sorry, there were some things missing in my reply.. these two tables (i.e.

TIME & TYPE) are joined with Combined fields like EmpID and Type i.e.

EmpIDType like this:

TIME:

LOAD EmpIDType, Hours INLINE [

EmpIDType, Hours

1-A, 8

2-B, 10

];

TYPE:

LOAD EmpIDType,Type, EmpName INLINE [

EmpIDType,Type,EmpName

1-A, A, EmpA

2-B, B, EmpB

2-C, C, EmpC

1-F, F, EmpC

2-B, B, EmpB

];

[swuehl]

I can't see why this is essentially different from my above sample, e.g. when looking at Type B (10 vs. 20 as result).

What is your open issue given your sample?

[#8]

My data is exactly joined in the same way as I have mentioned in the

attached sample. In theis sample I am getting same result while using Set

analysis and If condition but in my original data I am still getting

difference in results.

[swuehl]

You are getting same results in the charts, because you are grouping by EmpName, thus again forcing QV to join the tables.

If you are summing your Hours in global context, you are again getting different results.